pre_get_posts order by not working

Question

I am using the pre_get_posts hook to alter a query before it runs.

The posts in question are performances and so are date and time based.

I want to alter the query to get all performances which are in the future (i.e. the date is greater than today or the date is today and the time is greater than or equal to the current time). This works but when I try to order them primarily by date ascending and secondarily by time ascending the order is not as expected.

The posts are ordered by date correctly but only today's performances are ordered correctly by time with the times of performances on other days being ordered randomly.

I believe this has something to do with the naming of the meta_query arrays in relation to the orderby parameters but cannot work out how to solve this.

My code is as follows:

    //No limit on number to get
    $query->set('posts_per_page', -1);
    //Get future performances
    $query->set('meta_query', array(
        'relation'  => 'AND',   
        array(
            'relation'  => 'OR',
            'performance_date' => array(
                'key' => 'performance_date', 
                'value' => date('Ymd'),
                'compare' => '>',
                'type' => 'NUMERIC'
            ),
            array(
                'relation'  => 'AND',
                'performance_date' => array(
                    'key' => 'performance_date', 
                    'value' => date('Ymd'),
                    'compare' => '=',
                    'type' => 'NUMERIC'
                ),                      
                'performance_time' => array(
                    'key' => 'performance_time', 
                    'value' => date('H:i'),
                    'compare' => '>=',
                    'type' => 'TIME'
                )
            )               
        )
    ));
    //Order by closest performance first
    $query->set('orderby', array(
        'performance_date' => 'ASC',
        'performance_time' => 'ASC'
    )); 

Answer 1

I think this would be easier if you had the performance_datetime custom field, because that could simplify your meta query.

The following is just a demo fun for you to play with:

As far as I understand the problem, you get three INNER JOINS on the wp_posts table, namely wp_postmeta, mt1 and mt2. But the value of the meta_key field, for these joined tables, isn't always performance_date or performance_time. So ordering by the meta value, in one of those tables, will not be accurate for all the posts returned by WP_Query.

An hardcoded workaround, for only this case, could be:

ORDER BY 
   CASE
        WHEN wp_postmeta.meta_key = 'performance_date' 
            THEN CAST( wp_postmeta.meta_value AS SIGNED )
        WHEN mt1.meta_key = 'performance_date' 
            THEN CAST( mt1.meta_value AS SIGNED )
        WHEN mt2.meta_key = 'performance_date' 
            THEN CAST( mt2.meta_value AS SIGNED )
        ELSE
            wp_postmeta.post_id
    END ASC,
    CASE
        WHEN wp_postmeta.meta_key = 'performance_time' 
            THEN CAST( wp_postmeta.meta_value AS TIME)
        WHEN mt1.meta_key = 'performance_time' 
            THEN CAST( mt1.meta_value AS TIME)
        WHEN mt2.meta_key = 'performance_time' 
            THEN CAST( mt2.meta_value AS TIME)
        ELSE
            wp_postmeta.post_id
    END ASC

You could test this with:

$query->set('orderby', 'performance' );

where the custom ordering is supported by the following untested plugin:

add_filter( 'posts_orderby', function( $orderby, \WP_Query $q )
{
    if( 'performance' !== $q->get( 'orderby' ) )
        return $orderby;

    global $wpdb;

    $order = ( 'ASC' === strtoupper( $q->get( 'order' ) ) ) ? 'ASC' : 'DESC';
    $orderby = "
       CASE
            WHEN {$wpdb->postmeta}.meta_key = 'performance_date' 
                THEN CAST( {$wpdb->postmeta}.meta_value AS SIGNED )
            WHEN mt1.meta_key = 'performance_date' 
                THEN CAST( mt1.meta_value AS SIGNED )
            WHEN mt2.meta_key = 'performance_date' 
                THEN CAST( mt2.meta_value AS SIGNED )
            ELSE
                {$wpdb->postmeta}.post_id
       END {$order},
       CASE
            WHEN {$wpdb->postmeta}.meta_key = 'performance_time' 
                THEN CAST( {$wpdb->postmeta}.meta_value AS TIME)
            WHEN mt1.meta_key = 'performance_time' 
                THEN CAST( mt1.meta_value AS TIME)
            WHEN mt2.meta_key = 'performance_time' 
                THEN CAST( mt2.meta_value AS TIME)
            ELSE
                {$wpdb->postmeta}.post_id
        END {$order}";

    return $orderby;
}, 10, 2 );

Note that this is rather unflexible, becaue when you change your query, this might not work. But hopefully you can adjust this to your needs and even make it more flexible.