Ordering posts having multiple post-meta date fields

Question

Hello WordPress developers. Over the years, I've found answers to many questions I've had with WordPress. I want to thank you for your help. Today, I have a question myself, and seek for expert advice.

I'm building a site for a movie theatre. I have a custom post type movies. I'm using Types plugin to store the showdates and -times for each movie as a repeating date/time field. It stores the date/time as a Unix timestamp to postmeta table with meta_key "wpcf-showtime". Each movie has many showtimes, thus there is a number of meta_key/meta_value pairs having the same key, e.g.

post_id: 70, meta_key: wpcf-showtime, meta_value 1417548600
post_id: 70, meta_key: wpcf-showtime, meta_value 1417813200
post_id: 70, meta_key: wpcf-showtime, meta_value 1417896000

For the front page of the site, I want to show all ongoing movies till next week's Wednesday i.e. movies that have showtimes from now on, excluding movies that occur in the past or are marked as future (custom taxonomy).

My problem is ordering the movies; I want to show them ordered by showtime so, that the movie having the closest showtime to present time comes first.

This is the query I'm running:

$startdate = time();
$enddate = new DateTime('thursday next week');

$ongoing_movies = new WP_Query( array( 
    'post_type' => 'movies',
    'meta_key'  => 'wpcf-showtime',
    'orderby'   => 'meta_value_num', 
    'order'     => 'ASC',
    'tax_query' => array(
        array(
            'taxonomy' => 'genre',
            'field'    => 'slug',
            'terms'    => 'future', 
            'operator' => 'NOT IN'
        )
    ),
    'meta_query' => array(
        array(
            'key'      => 'wpcf-showtime',
            'value'    => array ( $startdate, $enddate->format('U')),
            'compare'  => 'BETWEEN', 
        ),
    )
));

This returns the correct movies, ordered by showtime. But the showtime it uses for sorting is the first value occurrence for meta_key "wpcf-showtime" stored for that particular movie. I.e. the first movie displayed may not be the one that has the next show, but the one that has the earliest showtime stored in db.

I would appreciate any help on finding an approach; pre_get_posts action, posts_orderby filter?

Answer 1

1) Using only the posts_orderby filter:

It looks like you want to order the movies by the minimum of the wpcf-showtime meta values.

You can modify the ORDER BY part of the SQL generated by the WP_Query, by using the posts_orderby filter.

Here's an example:

add_filter( 'posts_orderby', 'wpse_posts_orderby' );
$ongoing_movies = new WP_Query( $args );

where the filter callback is defined as:

/**
 *  Use MIN() on the meta value part ordering.
 */
function wpse_posts_orderby( $orderby )
{      
    global $wpdb;

    // Only run this callback once:
    remove_filter( current_filter(), __FUNCTION__ );

    // Replacement part:  
    $find    = "{$wpdb->postmeta}.meta_value+0 ";
    $replace = "mt1.meta_value+0 ";

    // Make sure we add MIN() to the correct part:    
    if( $find == str_ireplace( array( 'ASC', 'DESC' ), '', $orderby ) )
    {
        // Preserve the order direction:
        $orderby = str_ireplace(
            $find,
            "MIN( $replace) ",
            $orderby
        );
    }
    return $orderby;
}

Here we use mt1.meta_value, if the meta value restriction is the first argument array of the meta_query.

This will change:

ORDER BY wp_postmeta.meta_value+0 {ASC|DESC}

to

ORDER BY MIN( mt1.meta_value+0 ) {ASC|DESC}

2) Introducing a custom order parameter in WP_Query

You can try to use this setup:

$startdate = time();
$enddate = new DateTime('thursday next week');

$ongoing_movies = new WP_Query( array( 
    'post_type'    => 'movies',
    'meta_key'     => 'wpcf-showtime',
    'wpse_orderby' => 'meta_value_num_min',      // <-- New parameter to order by MIN!
    'orderby'      => 'meta_value_num', 
    'order'        => 'ASC',
    'tax_query'    => array(
        array(
            'taxonomy' => 'genre',
            'field'    => 'slug',
            'terms'    => 'future', 
            'operator' => 'NOT IN'
        )
    ),
    'meta_query' => array(
        array(
            'key'      => 'wpcf-showtime',
            'value'    => array ( $startdate, $enddate->format('U')),
            'compare'  => 'BETWEEN', 
            'type'     => 'UNSIGNED'     // <-- Let's override the default 'CHAR'
        ),
    )
));

where we use the following plugin to support the wpse_orderby parameter:

<?php
/**
 * Plugin Name: Modify the WP_Query ordering to support MAX and MIN
 * Description: Possible values of 'wpse_orderby' are  'meta_value_num_{min,max}'.
 * Plugin URI:  http://wordpress.stackexchange.com/a/173496/26350
 * Author:      Birgir Erlendsson (birgire)
 * Version:     0.0.2
 */ 

add_action( 'init', function(){
    if( class_exists( 'WPSE_Modify_Ordering' ) )
    {
        $o = new WPSE_Modify_Ordering;
        $o->init();
    }
});

class WPSE_Modify_Ordering
{
    private $type    = '';
    private $order   = '';
    private $orderby = '';

    public function init()
    {
        add_action( 'pre_get_posts', array( $this, 'pre_get_posts' ) );
    }

    public function pre_get_posts( WP_Query $q )
    {
        if( 
            in_array( 
                $q->get( 'wpse_orderby' ), 
                array( 'meta_value_num_min', 'meta_value_num_max' ) 
            ) 
        )
        {
            $this->type = strtoupper( substr( $q->get( 'wpse_orderby' ), -3 ) );
            $this->order = ( '' !== $q->get( 'order' ) ) ? $q->get( 'order' ) : 'ASC';
            add_filter( 'posts_orderby', array( $this, 'posts_orderby' ) );
        }
    }

    public function posts_orderby( $orderby )
    {
        remove_filter( current_filter(), array( $this, __FUNCTION__ ) );
        return " {$this->type}( mt1.meta_value+0 ) " . $this->order;
    }

} // end class

The wpse_orderby parameter overrides the native orderby parameter. It supports the values meta_value_num_min and meta_value_num_max.

Here we do the ordering on the mt1.meta_value, since the meta value restriction is the first argument array of the meta_query.

The reason we keep the orderby parameter, is to use it as a fallback if the above plugin is de-activated.

Hopefully you can modify this further to your needs.

Answer 2

This is a deceptively complex question; it's specifically about ordering by contextual date within the range of a query, when an object may have multiple dates; traditionally sorting and ordering uses the lowest value in the array of dates, which often results in incorrect ordering when querying a specific date range.

For reference, here the stripped back meat of the code I used in my own approach:

// Wrap query in filters to modify behaviour
// ==============  
add_filter('posts_join_paged','doty_custom_posts_join');
add_filter('posts_orderby','doty_custom_posts_orderby');
$new_query = new WP_Query( $args );
remove_filter('posts_orderby','doty_custom_posts_orderby');
remove_filter('posts_join_paged','doty_custom_posts_join');

// Hooks into the 'WHERE' component of the SQL query
// ==============
function doty_custom_posts_join($where) {
  global $wpdb,$query_dates;
  $start_date = (int)$query_dates[0]-86400;
  $where .= "LEFT JOIN (
        SELECT post_id,($start_date - MIN(meta_value)) as orderdate
        FROM $wpdb->postmeta
        WHERE meta_key = 'dates'
        AND meta_value > $start_date 
        GROUP BY post_id) AS date_orders
        ON $wpdb->posts.ID = date_orders.post_id";
  return $where;
}

// Hooks into the 'ORDER BY' component of the SQL query
// ==============
function doty_custom_posts_orderby($orderby) {
  $orderby = " date_orders.orderdate DESC";
  return $orderby;
}

It uses posts_join_pages and posts_orderby to change the way in which WP generates the SQL query which results from the normal WP_Query function. Specifically, it:

• Grabs the range's start date from a global var
• Builds a new LEFT JOIN to create an orderdate variable, which is selected from the nearest date from the array, and goes on to check that this is in the future.
• Uses the orderdate variable the sort key

I'm conscious that I've spent about 6 hours hacking this together, and that it's not the cleanest, but hopefully there's some value in it somewhere.