Query current and future events, ordered by begin date

Question

Using the Custom Post Type UI extension, I've created a 'Event' custom post-type, which fields are populated using custom fields (created with the extension Advanced Custom Fields) such as 'date-debut' and 'date-fin'.

On an archive page dedicated to this post-type, I'm trying to display the 10 next (current or future) events, ordered chronologically by beginning date.

The sorting part seems to be OK by itself; but it doesn't work anymore when I add the tests on dates, and anyway those don't work either.

Here is my code:

        $current_date = date('ymd');
        $my_query = new WP_Query( array ( 
        'post_type'         => 'mna_event',
        'posts_per_page'    => 10,
        'orderby'           => 'meta_value_num',
        'meta_key'          => 'date_debut',
        'order'             => 'ASC',
        'meta_query'        => array (
            'relation'      => 'OR',
            array (
                'key'       => 'date_debut',
                'value'     => $current_date,
                'compare'   => '>='
                ),
            array (
                'key'       => 'date_fin',
                'value'     => $current_date,
                'compare'   => '>='
                )
            )
        )
    );

Advice on the matter would be very much appreciated. Thanks

Answer 1

This happens because of the OR relation on meta_query and the way WordPress generates the actual query string. You need to leave out the meta_key and orderby from the query args and hook into the posts_clauses filter to modify the where and orderby pieces of the query:

function wpse_130954_orderby_fix($pieces){
    global $wpdb;
    $pieces['where']  .= " AND $wpdb->postmeta.meta_key = 'date_debut'";
    $pieces['orderby']  = "$wpdb->postmeta.meta_value ASC";
    return $pieces;
}

Add the filter before setting up your WP_Query object and then make sure to remove it after running your query to not affect other queries:

add_filter( 'posts_clauses', 'wpse_130954_orderby_fix', 20, 1 );
$current_date = date_i18n('Y-m-d');
$my_query = new WP_Query( array ( 
    'post_type'         => 'mna_event',
    'posts_per_page'    => 10,
    'meta_query'        => array (
        'relation'      => 'OR',
        array(
            'key'       => 'date_debut',
            'value'     => $current_date,
            'compare'   => '>=',
            'type'      => 'DATE'
            ),
        array(
            'key'       => 'date_fin',
            'value'     => $current_date,
            'compare'   => '>=',
            'type'   => 'DATE'
            )
        )
    )
);

$result = $my_query->posts;

remove_filter( 'posts_clauses', 'wpse_130954_orderby_fix', 20 );

UPDATE: Use yy-mm-dd format for your date fields on ACF settings. (yy-mm-dd in JS/ACF == Y-m-d in PHP/MySQL datetime format) I have updated the code above for this format. (updated $current_date format and added 'type' => 'DATE' in meta_query)