-
-
Save anunay/0c19d9be111067044cf1 to your computer and use it in GitHub Desktop.
query.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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, | |
| 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 m_file_id | |
| 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 | |
| GROUP BY dtt_i.tag_lang, dpp.id |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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,
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_idSEPARATOR ',') AS media_file_id,GROUP_CONCAT(DISTINCT dpm.
media_typeSEPARATOR ',') AS media_file_type,GROUP_CONCAT(DISTINCT dpm.
media_video_typeSEPARATOR ',') AS media_video_type,GROUP_CONCAT(DISTINCT dpl.
link_typeSEPARATOR ',') AS link_type,GROUP_CONCAT(DISTINCT dpl.
link_urlSEPARATOR ',') 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