Using ACF values in nested WP queries for CPT with date values in the past

Question

I'm using a pair of ACF date fields to create a query on a custom post type (event). I have a "Past Events" feed which is ignoring my usual technique for creating the following behaviour:

• IF event (CPT) has an end_date value, check it is in the past
• ELSE IF event does not have an end_date value, use the event_date value (required field) and check this is in the past

I’ve done this several times by nesting the two clauses in an OR relation, but in this case this isn’t working. Any event with an event_date value in the past is turning up in the “Past Events” query results, even when an end_date value is present and in the future.

Here’s the full query – it differs from my usual approach because it needs to filter out more options: event_type (radio button) and include_in_past_events (true/false).

date_default_timezone_set('Europe/London'); 
$past = date('Ymd', strtotime("-999 months")); 
$now = date('Ymd', strtotime("now")); 

$past_args = array(
 'post_type' => 'event',
 'meta_query' => array(
  'relation' => 'AND',
   array(
    'relation' => 'AND',
    array(
     'relation' => 'OR',
     // check to see if end date has been set
     array(
      'key' => 'end_date',
      'compare' => 'BETWEEN',
      'type' => 'NUMERIC',
      'value' => array($past, $now),
     ),
     // if no end date has been set use event/start date
     array(
      'key' => 'event_date',
      'compare' => 'BETWEEN',
      'type' => 'NUMERIC',
      'value' => array($past, $now),
     )
    ),
    array(
     'key' => 'event_type',
     'value' => array('Singlar', 'Multi-Day', 'Series'),
    )
   ),
   array(
    'key' => 'include_in_past_events',
    'value' => '1',
   )
  ),
  'meta_key'    => 'event_date',
  'orderby' => 'meta_value_num',
  'order' => 'DESC',
  'nopaging'    => false,
  'posts_per_page'=> '8'
 );

I can't work out why this isn't working for me, and would appreciate any insight.

Thanks!

Answer 1

Your current meta query embodies the logic

IF event_end IN range 
OR event_start IN range

You need to account for the conditions of event_end's presence separately:

IF event_end IN range
OR ( event_end NOT EXISTS AND event_start IN range )

Immediately adjacent nested clauses with the same AND/OR relation can be combined, which might make things a bit more readable.

a AND ( b AND ( c OR d ) ) === a AND b AND ( c OR d )
a OR ( b OR ( c AND d ) ) === a OR b OR ( c AND d )

You might also be able to name your clauses in order to order the results by which was used - but I haven't tested this.

All of the above in practice:

$past_args = array(
  'post_type'  => 'event',
  'meta_query' => array(
    'relation' => 'AND',
    array(
      'relation'   => 'OR',
      // check to see if end date has been set and is in range
      'end_clause' => array(
        'key'     => 'end_date',
        'compare' => 'BETWEEN',
        'type'    => 'NUMERIC',
        'value'   => array($past, $now),
      ),
      // if no end date has been set use event/start date
      array(
        'relation' => 'AND',
        array(
          'key'     => 'end_date',
          'compare' => 'NOT EXISTS',
        ),
        'start_clause' => array(
          'key'     => 'event_date',
          'compare' => 'BETWEEN',
          'type'    => 'NUMERIC',
          'value'   => array($past, $now),
        ),
      ),
    ),
    array(
      'key'   => 'event_type',
      'value' => array('Singlar', 'Multi-Day', 'Series'),
    ),
    array(
      'key'   => 'include_in_past_events',
      'value' => '1',
    )
  ),
  // 'meta_key'      => 'event_date',// not needed when ordering by clause name.
  'orderby'       => 'end_clause start_clause',
  'order'         => 'DESC',
  'nopaging'      => false,
  'posts_per_page'=> '8',
);