Support

Account

Home Forums General Issues Query posts where end and/or start date are in the past

Solved

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

  • 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!

  • 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
    ));
    
  • 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!

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

  • 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
    ));
    
  • 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 🙂

Viewing 6 posts - 1 through 6 (of 6 total)

The topic ‘Query posts where end and/or start date are in the past’ is closed to new replies.