I've been bashing against a bug for a few hours now, and can't seem to find my error. Could there be a bug with the relation type 'OR' in WordPress?
$evenements = new WP_Query(array( 'post_type' => 'evenements', 'orderby' => 'meta_value', 'meta_key' => 'startDate', 'order' => 'ASC', 'meta_query' => array( 'relation' => 'OR', array( 'key' => 'startDate', 'value' => date('Ymd'), 'compare' => '>=', ), array( 'key' => 'endDate', 'value' => date('Ymd'), 'compare' => '>=', ), ), ));
What is weird is that if I put relation to 'AND', it works as intended. If both statements are true, the post will show up. If I put relation to 'OR', all posts will show up, and for some reason even the order will not work properly. Even changing 'order' from 'ASC' to 'DESC' won't change the order the results come in.
To understand the query, here's the detail of what exactly I'm trying to do:
I want to show events that are in progress or in the future. The two metas are the date at which the event starts (startDate) and the date when the event ends (endDate).
So if the start date is higher or equal (to get if an event is happenning today) to today or the end date is higher or equal to today, show the post.
The second part (with the end date) is made so an event in progress will be shown, and I cant remove the first part (with the start date) because if the event only lasts a day, the client will only enter the startDate meta.
I discarded the bug coming from plugins since I disabled all plugins and the bug was still there.
Here is what I get if
SELECT SQL_CALC_FOUND_ROWS wp_posts.ID FROM wp_posts INNER JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id) INNER JOIN wp_postmeta AS mt2 ON (wp_posts.ID = mt2.post_id) JOIN wp_icl_translations t ON wp_posts.ID = t.element_id AND t.element_type = 'post_evenements' JOIN wp_icl_languages l ON t.language_code=l.code AND l.active=1 WHERE 1=1 AND wp_posts.post_type = 'evenements' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private') AND ( wp_postmeta.meta_key = 'startDate' OR ( mt1.meta_key = 'startDate' AND CAST(mt1.meta_value AS CHAR) >= '20140305' ) OR ( mt2.meta_key = 'endDate' AND CAST(mt2.meta_value AS CHAR) >= '20140305' ) ) AND t.language_code='fr' GROUP BY wp_posts.ID ORDER BY wp_postmeta.meta_value ASC LIMIT 0, 10