3

I have a Custom Post Type called "Films", with a Repeater field called "Showings", which have a field "start_datetime" of type DateTime...

I want to perform a query to get all those Films with a Showing in the next 7 days, and I want to order them by Showing's start_datetime.

I have this query:

$query = new \WP_Query( array(
    'numberposts'   => -1,
    'post_type'     => 'film',
    'meta_key'      => 'showings_%_start_datetime',
    'orderby'       => 'meta_value_num',
    'order'         => 'ASC',

    'meta_query'    => array(
        array(
            'key'       => 'showings_%_start_datetime',
            'value'     => array( time(), strtotime( '+7 days' ) ),
            'type'      => 'NUMERIC',
            'compare'   => 'BETWEEN'
        )
    )
));

This grabs all the films that have a showing in the next 7 days correctly, but then it orders the films by the start_datetime of the FIRST showing, and I need it to order them by the start_datetime of the FIRST FUTURE showing... Any help please?


Example:
Film A have a showing today.
Film B have a showing tomorrow and another showing 1 year ago.

My results will be ordered: Film B and then Film A (because actually Film B is the one with a showing starting eralier). But I need it to be ordered the other way around, because Film A is the film with the first future showing...

1

I discovered the problem by inspecting the actual SQL created by WP_Query. I realized that the generated SQL query has 2 INNER JOINs, one for the meta_key and another for the actual meta_query.

Basically on one hand it is filtering those films that have a showings_%_start_datetime in the next seven days, and on the other hand (independently) it is ordering the films by their showings_%_start_datetime value.

Just moving the meta_query parameters to the base query works fine:

query = new \WP_Query( array(
    'numberposts'   => -1,
    'post_type'     => 'film',
    'meta_key'      => 'showings_%_start_datetime',
    'meta_value'    => array( time(), strtotime( '+7 days' ) ),
    'meta_type'     => 'NUMERIC',
    'meta_compare'  => 'BETWEEN',
    'orderby'       => 'meta_value_num',
    'order'         => 'ASC',
));

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.