WordPress meta_query and order by custom field

Question

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...

Answer 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',
));