SHARE
TWEET

Untitled

a guest Aug 14th, 2019 70 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. with tag_table as (select template_id,
  2.                         string_agg(distinct tag_id, '|') as tag_id
  3.                         from dynamic_descriptions.dynamic_descriptions_template_tags
  4.                         group by 1
  5.                         ),
  6. synonym_table as (with synonymtext_table as (select slot_id,
  7.                         string_agg(distinct dynamic_descriptions.dynamic_descriptions_synonymtext.text, '|') as synonymtext
  8.                         from dynamic_descriptions.dynamic_descriptions_synonymtext
  9.                         group by 1)
  10. select distinct template_id,
  11. max(case when index = 1 then synonymtext end) as synonym_1,
  12. max(case when index = 2 then synonymtext end) as synonym_2,
  13. max(case when index = 3 then synonymtext end) as synonym_3,
  14. max(case when index = 4 then synonymtext end) as synonym_4,
  15. max(case when index = 5 then synonymtext end) as synonym_5,
  16. max(case when index = 6 then synonymtext end) as synonym_6,
  17. max(case when index = 7 then synonymtext end) as synonym_7,
  18. max(case when index = 8 then synonymtext end) as synonym_8,
  19. max(case when index = 9 then synonymtext end) as synonym_9,
  20. max(case when index = 10 then synonymtext end) as synonym_10,
  21. max(case when index = 11 then synonymtext end) as synonym_11,
  22. max(case when index = 12 then synonymtext end) as synonym_12
  23. from dynamic_descriptions.dynamic_descriptions_synonymslot
  24. inner join synonymtext_table
  25. on dynamic_descriptions.dynamic_descriptions_synonymslot.id = synonymtext_table.slot_id
  26. group by 1)
  27. select dynamic_descriptions.dynamic_descriptions_template.id as "template_id",
  28. dynamic_descriptions.dynamic_descriptions_template.category_id as "category_id",
  29. dynamic_descriptions.dynamic_descriptions_template.language_id as "locale",
  30. dynamic_descriptions.dynamic_descriptions_template.static,
  31. template_type_id as "template_type",
  32. dynamic_descriptions.dynamic_descriptions_variancename.name as "variance_name",
  33. dynamic_descriptions.dynamic_descriptions_template.text,
  34. title_table.title_1,
  35. title_table.title_2,
  36. title_table.title_3,
  37. tag_table.tag_id as "tags",
  38. synonym_table.synonym_1,
  39. synonym_table.synonym_2,
  40. synonym_table.synonym_3,
  41. synonym_table.synonym_4,
  42. synonym_table.synonym_5,
  43. synonym_table.synonym_6,
  44. synonym_table.synonym_7,
  45. synonym_table.synonym_8,
  46. synonym_table.synonym_9,
  47. synonym_table.synonym_10,
  48. synonym_table.synonym_11,
  49. synonym_table.synonym_12
  50. from dynamic_descriptions.dynamic_descriptions_template
  51. --TITLES TABLE
  52. left join (
  53. select distinct template_id,
  54.                 max(case when rn=1 then text end) as title_1,
  55.                 max(case when rn=2 then text end) as title_2,
  56.                 max(case when rn=3 then text end) as title_3
  57.             from            (
  58.                             select template_id,text,
  59.                             row_number() over(partition by template_id order by id) as rn
  60.                             from dynamic_descriptions.dynamic_descriptions_title
  61.                             ) as d_d_t
  62.                             group by 1
  63.             ) as title_table
  64.         on title_table.template_id = dynamic_descriptions.dynamic_descriptions_template.id
  65. --
  66. left join tag_table
  67.         on tag_table.template_id = dynamic_descriptions.dynamic_descriptions_template.id
  68. left join synonym_table
  69.         on synonym_table.template_id = dynamic_descriptions.dynamic_descriptions_template.id
  70. left join dynamic_descriptions.dynamic_descriptions_variancename
  71.         on dynamic_descriptions.dynamic_descriptions_template.variance_name_id = dynamic_descriptions.dynamic_descriptions_variancename.id
  72. where template_type_id in ('descriptive','topical') --set template types
  73. --and ((tag_table.tag_id ilike '%plural%' and not tag_table.tag_id ilike '%signup2%'))--tag_table.tag_id is null)) --tag filters
  74. --and (dynamic_descriptions.dynamic_descriptions_template.text ilike '% my %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% I %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% me % ' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% I''ve %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% I''m %')
  75. --and (dynamic_descriptions.dynamic_descriptions_template.text ilike '% our %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% we %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% us % ' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% we''ve %' or dynamic_descriptions.dynamic_descriptions_template.text ilike '% we''ll %')
  76. --and dynamic_descriptions.dynamic_descriptions_template.language_id in ('en-gb') --set language
  77. --and dynamic_descriptions.dynamic_descriptions_template.category_id in ('f02500_12','f03200_14') --set category
  78. order by 2
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top