展示・イベント

select distinct * from ( ( SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY m1.start DESC) as rno, wp_posts.*, m1.start as start, m1.end as end, '1' as type FROM wp_posts INNER JOIN ( SELECT pm.post_id, pm.meta_value AS 'start', pm1.meta_value AS 'end' FROM wp_postmeta pm INNER JOIN wp_postmeta AS pm1 ON ( pm.post_id = pm1.post_id AND pm.meta_key LIKE 'setting-dates\_%\_period\_%\_start' AND pm1.meta_key LIKE 'setting-dates\_%\_period\_%\_end' AND SUBSTRING_INDEX(pm.meta_key, '_', 4) = SUBSTRING_INDEX(pm1.meta_key, '_', 4) ) ) AS m1 ON ( m1.post_id = wp_posts.ID AND wp_posts.post_type = 'event' ) WHERE (m1.start = '20190525' AND (m1.end IS NULL OR m1.end = '') OR m1.start <= '20190525' AND m1.end >= '20190525') AND wp_posts.post_status = 'publish' AND (wp_posts.post_title = '' OR wp_posts.post_title LIKE '%![:ja!]%' ESCAPE '!' OR wp_posts.post_title LIKE '%%' OR (wp_posts.post_title NOT LIKE '%![:!]%' ESCAPE '!' AND wp_posts.post_title NOT LIKE '%%')) ORDER BY rno ASC limit 10000 ) union all ( SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY m2.start ASC) as rno, wp_posts.*, m2.start as start, m2.end as end, '2' as type FROM wp_posts INNER JOIN ( SELECT pm.post_id, pm.meta_value AS 'start', pm1.meta_value AS 'end' FROM wp_postmeta pm INNER JOIN wp_postmeta AS pm1 ON ( pm.post_id = pm1.post_id AND pm.meta_key LIKE 'setting-dates\_%\_period\_%\_start' AND pm1.meta_key LIKE 'setting-dates\_%\_period\_%\_end' AND SUBSTRING_INDEX(pm.meta_key, '_', 4) = SUBSTRING_INDEX(pm1.meta_key, '_', 4) ) ) AS m2 ON ( m2.post_id = wp_posts.ID AND wp_posts.post_type = 'event' ) WHERE (m2.start > '20190525') AND wp_posts.post_status = 'publish' AND (wp_posts.post_title = '' OR wp_posts.post_title LIKE '%![:ja!]%' ESCAPE '!' OR wp_posts.post_title LIKE '%%' OR (wp_posts.post_title NOT LIKE '%![:!]%' ESCAPE '!' AND wp_posts.post_title NOT LIKE '%%')) group by wp_posts.id ORDER BY rno ASC limit 10000 ) union all ( SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY start ASC) as rno, wp_posts.*, wp_posts.post_date AS start, wp_posts.post_date AS end, '3' as type FROM wp_posts INNER JOIN ( SELECT pt.ID as 'post_id', pt.post_date as 'post_date' FROM wp_posts pt INNER JOIN wp_term_relationships AS tr ON (tr.object_id = pt.ID) INNER JOIN wp_term_taxonomy AS tt ON (tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'eventcat') INNER JOIN wp_terms AS ts ON (tt.term_id = ts.term_id AND ts.slug = 'permanent') WHERE pt.post_type = 'event') AS tm ON (tm.post_id = wp_posts.ID) ORDER BY wp_posts.post_date DESC limit 10000 ) union all ( SELECT DISTINCT ROW_NUMBER() OVER (ORDER BY m3.start DESC) as rno, wp_posts.*, m3.start as start, m3.end as end, '4' as type FROM wp_posts INNER JOIN ( SELECT pm.post_id, pm.meta_value AS 'start', pm1.meta_value AS 'end' FROM wp_postmeta pm INNER JOIN wp_postmeta AS pm1 ON ( pm.post_id = pm1.post_id AND pm.meta_key LIKE 'setting-dates\_%\_period\_%\_start' AND pm1.meta_key LIKE 'setting-dates\_%\_period\_%\_end' AND SUBSTRING_INDEX(pm.meta_key, '_', 4) = SUBSTRING_INDEX(pm1.meta_key, '_', 4) ) ) AS m3 ON ( m3.post_id = wp_posts.ID AND wp_posts.post_type = 'event' ) WHERE (m3.start < '20190525' AND ( (m3.end IS NULL OR m3.end = '') AND NOT EXISTS (select * from wp_postmeta pmt where pmt.post_id = m3.post_id AND pmt.meta_key LIKE 'setting-dates\_%\_period\_%\_start' AND pmt.meta_value > '20190525' ) ) OR m3.start < '20190525' AND m3.end IS NOT NULL AND m3.end <> '' AND m3.end < '20190525') AND wp_posts.post_status = 'publish' AND (wp_posts.post_title = '' OR wp_posts.post_title LIKE '%![:ja!]%' ESCAPE '!' OR wp_posts.post_title LIKE '%%' OR (wp_posts.post_title NOT LIKE '%![:!]%' ESCAPE '!' AND wp_posts.post_title NOT LIKE '%%')) group by wp_posts.id ORDER BY rno DESC limit 10000 ) ) AS combine where 1 = 1 and combine.post_status = 'publish' and (combine.post_title='' OR combine.post_title LIKE '%![:ja!]%' ESCAPE '!' OR combine.post_title LIKE '%%' OR (combine.post_title NOT LIKE '%![:!]%' ESCAPE '!' AND combine.post_title NOT LIKE '%%')) and combine.start <= '20160101' and combine.end >= '20160101' GROUP BY ID ORDER BY type ASC, rno ASC LIMIT 0,10;