pre_get_posts order by not working

Question

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.

Related Posts: