Using OR relation in meta_query to check for a value before sorting by another

Question

I am writing a pair of queries for a site with an events listing function. Events can be either on a single date or over several (multi-day). All events have a value in custom field event_date. Multi-day events also have an end_date value.

Single day events should be in the 'upcoming events' list (first query) until the end of their event_date, and multi-day events should stay until the end of their end_date. Once they are no longer in the upcoming list they should move to the 'past events' list (new query, same page).

The upcoming query works perfectly. Multi-day events stay in the list between their event_date and end_date values:

$now     = date('Ymd', strtotime("now"));
$future  = date('Ymd', strtotime("+24 months"));
$uc_args = array(
    'post_type'  => 'event',
    'meta_query' => array(
        'relation' => 'OR',
        // check post has end_date and it is between $now and $future
        array(
            'key'     => 'end_date',
            'compare' => 'BETWEEN',
            'type'    => 'NUMERIC',
            'value'   => array($now, $future),
        ),
        // if no end date check event_date is between $now and $future
        array(
            'key'     => 'event_date',
            'compare' => 'BETWEEN',
            'type'    => 'NUMERIC',
            'value'   => array($now, $future),
        ),
    ),
    'meta_key' => 'event_date',
    'orderby'  => 'meta_value',
    'order'    => 'ASC',
    'nopaging' => true,
);

The past query is constructed exactly the same way, but multi-day events are appearing once their event–date has passed, even if they have an end_date value:

$past    = date('Ymd', strtotime("-999 months"));
$now     = date('Ymd', strtotime("now"));
$uc_args = array(
    'post_type'  => 'event',
    'meta_query' => array(
        'relation'  => 'OR',
        // check post has end_date and it is between $past and $now
        array(
            'key'     => 'end_date',
            'compare' => 'BETWEEN',
            'type'    => 'NUMERIC',
            'value'   => array($past, $now),
        ),
        // if no end date check event_date is between $past and $now
        array(
            'key'     => 'event_date',
            'compare' => 'BETWEEN',
            'type'    => 'NUMERIC',
            'value'   => array($past, $now),
        ),
    ),
    'meta_key'       => 'event_date',
    'orderby'        => 'meta_value',
    'order'          => 'DESC',
    'nopaging'       => false,
    'posts_per_page' => '8',
);

I can see how an OR relation would include all posts which meet either criteria, but it doesn't do this in the first query. So far as I know there is no ELSE relation but that is the idea I am aiming for here.

Why does this work for upcoming events but not for past events?

Answer 1

I think this might be part of the problem in your past query (the second one)

        array(
            'key'     => 'end_date',
            'compare' => 'BETWEEN',
            'type'    => 'NUMERIC',
            'value'   => array($past, $now),
        ),

You're using BETWEEN but I think something like < would be what you want. You don't want to show events that have ended between $past and $now, right? You want to show events that have ended in general.

        array(
            'key'     => 'end_date',
            'compare' => '<',
            'type'    => 'NUMERIC',
            'value'   => $now,
        ),