shvv

Untitled

Jan 4th, 2021
1,135
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

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×