solr.sql

solr.sql

SELECT 
DATE_FORMAT(dpp.created, '%Y-%m-%dT%TZ') AS press_created, 
dpp.press_source_id,
dss.source_name AS press_source_name,
dpp.id press_pk_id, 
dpp.press_id, 
dtt_i.tag_lang,
dcc.country_name AS press_country_name,
dcc.country_keywords AS press_country_keywords,
dss.source_slug, 
dss.source_logo,
GROUP_CONCAT(DISTINCT dtt_i.tag_id SEPARATOR ',') AS tag_i_ids, 
GROUP_CONCAT(DISTINCT dtt_i.tag_name SEPARATOR ',') AS tag_i_names,
 
GROUP_CONCAT(DISTINCT dtt_s.tag_id SEPARATOR ',') AS tag_s_ids, 
GROUP_CONCAT(DISTINCT dtt_s.tag_name SEPARATOR ',') AS tag_s_names,
 
GROUP_CONCAT(DISTINCT dtt_c.tag_id SEPARATOR ',') AS tag_c_ids, 
GROUP_CONCAT(DISTINCT dtt_c.tag_name SEPARATOR ',') AS tag_c_names,
 
GROUP_CONCAT(DISTINCT dpm.media_file_id SEPARATOR ',') AS media_file_id, 
GROUP_CONCAT(DISTINCT dpm.media_type SEPARATOR ',') AS media_file_type, 
GROUP_CONCAT(DISTINCT dpm.media_video_type SEPARATOR ',') AS media_video_type,
 
GROUP_CONCAT(DISTINCT dpl.link_type SEPARATOR ',') AS link_type, 
GROUP_CONCAT(DISTINCT dpl.link_url SEPARATOR ',') AS link_url,
 
dpp.*
 
FROM default_press_presses AS dpp
 
LEFT JOIN default_press_presses_tags AS dppt_i ON dppt_i.press_id = dpp.press_id AND dppt_i.tag_type = 'i'
LEFT JOIN default_tag_tags AS dtt_i ON dtt_i.tag_id = dppt_i.tag_id AND dtt_i.tag_lang = dpp.press_lang
 
LEFT JOIN default_press_presses_tags AS dppt_s ON dppt_s.press_id = dpp.press_id AND dppt_s.tag_type = 's'
LEFT JOIN default_tag_tags AS dtt_s ON dtt_s.tag_id = dppt_s.tag_id AND dtt_s.tag_lang = dpp.press_lang
 
LEFT JOIN default_press_presses_tags AS dppt_c ON dppt_c.press_id = dpp.press_id AND dppt_c.tag_type = 'c'
LEFT JOIN default_tag_tags AS dtt_c ON dtt_c.tag_id = dppt_c.tag_id AND dtt_c.tag_lang = dpp.press_lang
 
LEFT JOIN default_country_countries_tags AS dcct ON dcct.tags_id = dtt_c.tag_id
LEFT JOIN default_country_countries AS dcc ON dcc.country_id = dcct.countries_id AND dcc.country_lang = dpp.press_lang
 
LEFT JOIN default_source_sources AS dss ON dss.id = dpp.press_source_id
 
LEFT JOIN default_press_medias AS dpm ON dpm.media_press_id = dpp.press_id
 
LEFT JOIN default_press_links AS dpl ON dpl.link_press_id = dpp.press_id
 
GROUP BY dtt_i.tag_lang, dpp.id