1

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'
    )); 
  • Is there a reason you have a date and time stored separately? I did something similar recently and I believe I joined the date and time to make a single datetime custom field and then ordered by that. – Cai Feb 22 '16 at 14:13
2

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.

  • I have combined the date and time fields when a post is updated so that I can query it as one field. The reason it is two fields is that I use Advanced Custom Fields Pro to build post type editing forms and there is no date and time field so I have to use a date field and separate a text field for the time. – Michael Feb 22 '16 at 16:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.