Query posts where end and/or start date are in the past

• Twansparant

• January 5, 2016 at 10:27 pm

Hi there,

This is more of a general WordPress question, but it might be useful for other ACF users. I have an event post type with 2 datepicker fields: acf_event_date_start & acf_event_date_end.

I want to display two separate queries, 1 for upcoming events and one for past events. That is simple, but the thing is the acf_event_date_end is not a required field.

So for my upcoming events I can do this:

/* Upcoming Events */
$wp_query = new WP_Query(array(
	'post_type' 	 => 'event',
	'meta_query'   => array(
  	'relation'      => 'OR',
  	// check to see if end date has been set
    array(
      'key'         => 'acf_event_date_end',
      'value'       => date('Ymd'),
      'compare'     => '>=',
      'type'        => 'date'
    ),
    // if no end date has been set use start date
    array(
      'key'         => 'acf_event_date_start',
      'value'       => date('Ymd'),
      'compare'     => '>=',
      'type'        => 'date'
    )
  ),
	'orderby' 		 => 'meta_value_num',
	'order'        => 'ASC',
	'nopaging'     => true
));

Which works great!
So for my past events I tried the following:

/* Past Events */
$paged    = (get_query_var('paged')) ? get_query_var('paged') : 1;
$wp_query = new WP_Query( array(
	'post_type' 	 => 'event',
	'meta_query'   => array(
  	'relation'      => 'OR',
    // check to see if end date has been set
    array(
      'key'         => 'acf_event_date_end',
      'value'       => date('Ymd'),
      'compare'     => '<',
      'type'        => 'date'
    ),
    // if no end date has been set use start date
    array(
      'key'         => 'acf_event_date_start',
      'value'       => date('Ymd'),
      'compare'     => '<',
      'type'        => 'date'
    )
  ),
	'orderby' 		=> 'meta_value_num',
	'order'       => 'DESC',
	'paged' 			=> $paged
));

Which works, but the events where the acf_event_date_end is in the future are also in the query. So I tried this:

$wp_query = new WP_Query( array(
	'post_type' 	 => 'event',
	'meta_query'   => array(
  	'relation'      => 'OR',
    // check to see if end date has been set
  	array(
      'relation'    => 'AND',
      array(
        'key'         => 'acf_event_date_end',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      ),
      array(
        'key'         => 'acf_event_date_start',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      )
    ),
    // if no end date has been set use start date
    array(
      'key'         => 'acf_event_date_start',
      'value'       => date('Ymd'),
      'compare'     => '<',
      'type'        => 'date'
    )
  ),
	'orderby' 		=> 'meta_value_num',
	'order'       => 'DESC',
	'paged' 			=> $paged
));

But that messes up the query completely.
So my question is:

How can I query my event posts where:
IF there is a acf_event_date_end value, it’s in the past AND the acf_event_date_start value is in the past.
OR the acf_event_date_start value is in the past.

Thanks in advance!

• John Huebner

• January 7, 2016 at 12:16 pm

Try this

$wp_query = new WP_Query( array(
  'post_type'    => 'event',
  'meta_query'   => array(
    'relation'      => 'OR',
    // if start date and end date are < now
    // OR
    // if start date < now and end date is empty
    array(
      'relation'    => 'AND',
      // if start date < now and end date is emptye
      array(
        'key'         => 'acf_event_date_start',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      ),
      array(
        'key'         => 'acf_event_date_end',
        'value'       => '',
        'compare'     => '='
      )
    ),
    array(
      'relation' => 'AND',
      // if start date and end date < now
      array(
        'key'         => 'acf_event_date_start',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      ),
      array(
        'key'         => 'acf_event_date_end',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      )
    )
  ),
  'orderby'     => 'meta_value_num',
  'order'       => 'DESC',
  'paged'       => $paged
));

• Twansparant

• January 7, 2016 at 9:40 pm

Wow thanks, that works perfectly!
I ended up doing this:

/* Ongoing Past Events */
$ongoing = new WP_Query( array(
  'post_type' 	 => 'event',
	'meta_query'   => array(
  	'relation'      => 'AND',
    array(
      'key'         => 'acf_event_date_start',
      'value'       => date('Ymd'),
      'compare'     => '<',
      'type'        => 'date'
    ),
    array(
      'key'         => 'acf_event_date_end',
      'value'       => date('Ymd'),
      'compare'     => '>=',
      'type'        => 'date'
    )
  ),
	'orderby' 		=> 'meta_value_num',
	'order'       => 'DESC'
));
if ($ongoing->have_posts()) :
  $exclude = array();
  while($ongoing->have_posts()) : $ongoing->the_post();
    $exclude[] = $post->ID;       
  endwhile;
endif;

/* Past Events */
$paged    = (get_query_var('paged')) ? get_query_var('paged') : 1;
$wp_query = new WP_Query( array(
	'post_type' 	 => 'event',
	'post__not_in' => $exclude, // Exclude ongoing past events
	'meta_query'   => array(
    array(
      'key'         => 'acf_event_date_start',
      'value'       => date('Ymd'),
      'compare'     => '<',
      'type'        => 'date'
    )
  ),
	'orderby' 		=> 'meta_value_num',
	'order'       => 'DESC',
	'paged' 			=> $paged
));

Which also works, but you need 2 queries which is not ideal.
Thanks for your help!

• Twansparant

• January 7, 2016 at 9:53 pm

Sorry, I spoke too soon…
I think the query itself works, but the post order is all messed up.
The posts with both start and end date are in between the posts with only a start date in the wrong order.

• John Huebner

• January 8, 2016 at 11:07 am

I’m not sure how to fix that. You might try setting the ‘meta_key’ query argument to the start date field name, but I don’t know it that will work

$wp_query = new WP_Query( array(
  'post_type'    => 'event',
  'meta_query'   => array(
    'relation'      => 'OR',
    // if start date and end date are < now
    // OR
    // if start date < now and end date is empty
    array(
      'relation'    => 'AND',
      // if start date < now and end date is emptye
      array(
        'key'         => 'acf_event_date_start',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      ),
      array(
        'key'         => 'acf_event_date_end',
        'value'       => '',
        'compare'     => '='
      )
    ),
    array(
      'relation' => 'AND',
      // if start date and end date < now
      array(
        'key'         => 'acf_event_date_start',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      ),
      array(
        'key'         => 'acf_event_date_end',
        'value'       => date('Ymd'),
        'compare'     => '<',
        'type'        => 'date'
      )
    )
  ),
  'meta_key'    => 'acf_event_date_start',
  'orderby'     => 'meta_value_num',
  'order'       => 'DESC',
  'paged'       => $paged
));

• zeshanshani

• February 2, 2017 at 8:59 pm

I tried above solutions, unfortunately, none worked out for me. When I used ‘OR’ in the first wp_query argument, it hung up the page.

I ended up accidentally writing this code that solved the issue for me:

'meta_query' => array(
    'relation' => 'OR',
    array(
        'key'     => 'date_start',
        'value'   => date( 'Ymd' ),
        'compare' => '=',
        'type'    => 'date'
    ),
    array(
        'relation' => 'AND',
        array(
            'key'     => 'date_start',
            'value'   => date( 'Ymd' ),
            'compare' => '<=',
            'type'    => 'date'
        ),
        array(
            'key'     => 'date_end',
            'value'   => date( 'Ymd' ),
            'compare' => '>=',
            'type'    => 'date'
        ),
    ),
)

Gist for description: https://gist.github.com/842c14baeb42d33ce90d1d1bf7a5de68

Not sure why the previous code didn’t work for me as the logic seems perfectly fine. I guess it’s just way to complex to be a query 🙂