WP_Query using meta_query with relation OR and orderby meta_value doesn’t work

7 Views

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.

Read More

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 echo $evenements->request. 

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
Post Views: 7

Related posts

1 comment

  1. The issue is that WordPress are getting all posts that has a ‘startDate’ meta key, no matter the meta value.

    You can understand that form this part of the request:

    ...
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' )
)
...

    so if a post has a the meta_key ‘startDate’ it will be returned.

    The culprit of this behavior is the 'meta_key' argument

    ...
new WP_Query( array(
    'post_type'     => 'evenements',
    'orderby'       => 'meta_value',
    'meta_key'      => 'startDate', // <-- this is the culprit
    ...

    However, if you remove it you will not be able to order by a meta value, because, sometimes WordPress is… (I don’t know the english term, complete the sentence by yourself).

    The solution is (should be) put the first part of your meta query (regarding start date), as plain arguments (not part of meta_query), and the second part (regarding end date) inside meta_query array:

    $evenements = new WP_Query(array(
  'post_type'      => 'evenements',
  'meta_key'       => 'startDate',
  'meta_value'     => date('Ymd'),
  'meta_type'      => 'NUMERIC',
  'meta_compare'   => '>=',
  'orderby'        => 'meta_value',
  'order'          => 'ASC',
  'meta_query'     => array(
    'relation'  => 'OR',
     array (
       'key'     => 'endDate',
       'value'   => date('Ymd'),
       'compare' => '>=',
       'type'    => 'NUMERIC'
     )
  )
));

    Your setup is too complex to make me test it accurately, so try it yourself and let me know.

Comments are closed.