Query between two meta keys

Question

I feel like I'm extremely close on this one but the query keeps on showing up empty. Basically, I'm trying to query the custom values between two dates. Records should be returned if the start date is less than the current date and the end date is greater than or equal to the current date:

$args = array(
    'taxonomy' => 'exhibition_type', 
    'term' => 'faculty',
    'numberposts' => 10,
    'post_type' => 'exhibitions',
    'meta_key' => 'start_date_of_event',
    'meta_query' => array(
        'relation' => 'AND',
            array(
                'key' => 'start_date_of_event',
                'value' => $current,
                'compare' => '<'
            ),
            array(
                'key' => 'end_event_date',
                'value' => $current,
                'compare' => '>='
            )
        ),
    'order_by' => 'meta_value_num',
    'order' => 'ASC',
    'paged' =>  $paged
);

The code I'm using now keeps on turning up 0 results and I'm stuck at the moment. Any help would be much appreciated.

Answer 1

Update - it may be supported in a meta_query. I need more information - see below.

You can't do it using a meta_query, it's not supported.

Do you have data in meta_data fields? - WordPress queries will exclude posts where any of the orderby (or meta_query) fields is missing. WordPress adds a join condition to the SQL query to support the orderby or query fields, an inner join (a join which only includes posts with the join field.)

If the the orderby 'inner join' is the issue, if you need to include posts where this field may be missing, then you can filter the join and orderby clauses of the generated SQL

If you're still not getting results, it would be useful to see the actual SQL that's being generated. Add

<pre><?php echo your_query_object->request; ?></pre> 

to your template to display it, and add it to your question as an update.

Answer 2

Have you tried specifying the 'type' of data for the meta_query? I'm assuming you're using timestamps because I've written exactly this before now and have it working.

Eg:

$args = array(
    ...
    'meta_query' => array(
        'relation' => 'AND',
            array(
                'key' => 'start_date_of_event',
                'value' => $current,
                'compare' => '<',
                'type' => 'NUMERIC'
            ),
            array(
                'key' => 'end_event_date',
                'value' => $current,
                'compare' => '>=',
                'type' => 'NUMERIC'
            )
        ),
    ...
);

It'd be awesome if WP_Query supported sorting on different meta data types other than strings or numbers then you could actually use proper DATE and DATETIME comparisons :(