Here's the case - I have the following query:
SELECT DISTINCT post_id FROM ( ( SELECT DISTINCT rp.post_id, pm.meta_value AS premiere_date FROM wp_related_people AS rp JOIN wp_posts AS p ON rp.post_id = p.ID JOIN wp_postmeta AS pm ON pm.post_id = p.ID WHERE rp.person_id = 494166 AND rp.role = 'director' AND pm.meta_key = 'htk_premiere_date' ) UNION ( SELECT DISTINCT rp.post_id, '' AS premiere_date FROM wp_related_people AS rp JOIN wp_posts AS p ON rp.post_id = p.ID JOIN wp_postmeta AS pm ON pm.post_id = p.ID WHERE rp.person_id = 494166 AND rp.role = 'director' ) ORDER BY (premiere_date IS NULL), premiere_date DESC ) AS element_ids
The above query works as intended but, as those two inner SELECTs are fairly similar, I was wondering if there is a way to merge them into a single query?
Here's the background: This is related to a WordPress database, expanded with custom tables. I need to get IDs of elements, sorted by htk_premiere_date meta key value. This key & its value is stored in wp_postmeta - table that has the following columns: meta_id (not important here), post_id, meta_key & meta_value. The point is, I need to sort all IDs in the following pattern:
- First, IDs that have this htk_premiere_date key set and it's assigned a date value (so sorted by date).
- Second, IDs that does not have the htk_premiere_date meta value set, but the key has its row in the wp_postmeta table.
- Third - and this is basically the source of the whole problem - if a post ID does not have a row in wp_postmeta table for htk_premiere_date key, it should be treated as in point 2. - so as if it has the key but no value assigned to it.
Thanks a lot for any help!