Ordering posts having multiple post-meta date fields

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?

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.