shvv

Untitled

Jan 4th, 2021
1,004
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.   queryParams
  3.   https://euvsdisinfo.eu/disinformation-cases/?text=Ukraine+war&date=01.01.2020+-+31.12.2020&disinfo_language%5B0%5D=ara&disinfo_language%5B1%5D=rus&disinfo_countries%5B0%5D=77544&disinfo_countries%5B1%5D=77547&per_page=30&offset=30&orderby=outlets&order=DESC
  4.   */
  5. select sql_calc_found_rows report.id                                                      id,
  6.                            report.post_title,
  7.                            report.post_content,
  8.                            group_concat(distinct keyword_post.post_title)                 keywords,
  9.                            group_concat(distinct country_post.post_title)                 countries,
  10.                            group_concat(distinct d_outlet.name, ',', d_outlet.identifier) outlets,
  11.                            group_concat(distinct d_language.code)                         language_codes,
  12.                            COALESCE(group_concat(d_outlet.name), ~0)                      outlet_name
  13. from wp_posts report
  14.          left join wp_postmeta keyword_meta
  15.                    on report.id = keyword_meta.post_id and keyword_meta.meta_key = 'disinfo_keyword_posts'
  16.          left join wp_posts keyword_post on keyword_meta.meta_value = keyword_post.ID
  17.          left join wp_postmeta country_meta
  18.                    on report.id = country_meta.post_id and country_meta.meta_key = 'disinfo_country_posts'
  19.          left join wp_posts country_post on country_meta.meta_value = country_post.ID
  20.          left join wp_disinformation_link d_link on report.id = d_link.claim_id
  21.          left join wp_disinformation_outlet d_outlet on d_link.author_id = d_outlet.id
  22.          left join wp_postmeta report_date on report.id = report_date.post_id and report_date.meta_key = 'disinfo_date'
  23.          left join wp_disinformation_language d_language on d_outlet.in_language_id = d_language.id
  24. where report.post_type = 'report'
  25.   and report.post_status = 'publish'
  26.   and cast(report_date.meta_value as date) >= '2020-1-1'
  27.   and cast(report_date.meta_value as date) <= '2020-12-31'
  28.   and country_meta.meta_value in (77544, 77547)
  29. group by report.ID
  30. having (report.post_title like '%Ukraine%' or report.post_content like '%Ukraine%' or
  31.         keywords like '%Ukraine%' or countries like '%Ukraine%' or outlets like '%Ukraine%')
  32.    and (report.post_title like '%war%' or report.post_content like '%war%' or
  33.         keywords like '%war%' or countries like '%war%' or outlets like '%war%')
  34.    and ('ara' in (language_codes) or 'rus' in (language_codes))
  35. order by outlet_name DESC
  36. limit 30, 30
RAW Paste Data