/*
  queryParams
  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
  */
select sql_calc_found_rows report.id                                                      id,
                           report.post_title,
                           report.post_content,
                           group_concat(distinct keyword_post.post_title)                 keywords,
                           group_concat(distinct country_post.post_title)                 countries,
                           group_concat(distinct d_outlet.name, ',', d_outlet.identifier) outlets,
                           group_concat(distinct d_language.code)                         language_codes,
                           COALESCE(group_concat(d_outlet.name), ~0)                      outlet_name
from wp_posts report
         left join wp_postmeta keyword_meta
                   on report.id = keyword_meta.post_id and keyword_meta.meta_key = 'disinfo_keyword_posts'
         left join wp_posts keyword_post on keyword_meta.meta_value = keyword_post.ID
         left join wp_postmeta country_meta
                   on report.id = country_meta.post_id and country_meta.meta_key = 'disinfo_country_posts'
         left join wp_posts country_post on country_meta.meta_value = country_post.ID
         left join wp_disinformation_link d_link on report.id = d_link.claim_id
         left join wp_disinformation_outlet d_outlet on d_link.author_id = d_outlet.id
         left join wp_postmeta report_date on report.id = report_date.post_id and report_date.meta_key = 'disinfo_date'
         left join wp_disinformation_language d_language on d_outlet.in_language_id = d_language.id
where report.post_type = 'report'
  and report.post_status = 'publish'
  and cast(report_date.meta_value as date) >= '2020-1-1'
  and cast(report_date.meta_value as date) <= '2020-12-31'
  and country_meta.meta_value in (77544, 77547)
group by report.ID
having (report.post_title like '%Ukraine%' or report.post_content like '%Ukraine%' or
        keywords like '%Ukraine%' or countries like '%Ukraine%' or outlets like '%Ukraine%')
   and (report.post_title like '%war%' or report.post_content like '%war%' or
        keywords like '%war%' or countries like '%war%' or outlets like '%war%')
   and ('ara' in (language_codes) or 'rus' in (language_codes))
order by outlet_name DESC
limit 30, 30