Support

Account

Home Forums General Issues Ordering/Filtering wp_query using ACF Date field

Solved

Ordering/Filtering wp_query using ACF Date field

  • I’m working at this (dev) site, trying to put together a pair of queries which call the custom post type ‘event’ over a four-year period, ordered by ACF date picker data and in relation to the current time.

    http://wpa.matmartin.co.uk/events/

    The issue is that some events have a single date (‘date’ – required) but others have a start date (‘date’) and end date (‘end_date’ – optional). I need the queries to check for end_date before sorting by date, so that events which are ongoing (i.e. current time is between start and end dates) appear still in the first of the two queries, and do not appear in the second until their end date has passed.

    Currently the first query is working fine like this:

    <h4>Upcoming Events</h4>
    <?php 
    //Set server timezone to GMT
    date_default_timezone_set('Europe/London'); 
    //Today's date
    $date_1 = date('Ymd', strtotime("now")); 
    //Future date - the arg will look between today's date and this future date to see if the post fall within the 2 dates.
    $date_2 = date('Ymd', strtotime("+24 months"));
    ?>
    <?php
    $upcoming_args = array(
    'post_type'		=> 'event',
    'meta_query'	=> array(
    	'relation'	=> 'OR',
    	// check to see if end date has been set
    	array(
    	'key'		=> 'end_date',
    	'compare'	=> 'BETWEEN',
    	'type'		=> 'numeric',
    	'value'		=> array($date_1, $date_2),
    	),
    	// if no end date has been set use start date
    	array(
    	'key'		=> 'date',
    	'compare'	=> 'BETWEEN',
    	'type'		=> 'numeric',
    	'value'		=> array($date_1, $date_2),
    	)
    ),
    'orderby'	=> 'meta_value_num',
    'order'		=> 'ASC',
    'nopaging'	=> true
    );
    ?>	
    <?php 
    // the upcoming events query
    $upcoming_query = new WP_Query( $upcoming_args ); 
    ?>
    <?php if ( $upcoming_query->have_posts() ) : ?>
    <?php while ( $upcoming_query->have_posts() ) : $upcoming_query->the_post(); ?>
    
    	<!-- do stuff -->
    
    <?php endwhile; ?>
    <?php wp_reset_postdata(); ?>
    <?php else:  ?>
    <p><?php _e( 'There are currently no upcoming events to list, please check back soon.' ); ?></p>
    <?php endif; ?>

    So my problem is with the second query, which I don’t seem to be able to get not to show events with start dates in the past even when their end dates are in the future:

    <h4>Recent Events</h4>
    <?php 
    //Set server timezone to GMT
    date_default_timezone_set('Europe/London'); 
    //Past cutoff date - the arg will look between today's date and this future date to see if the post fall within the 2 dates.
    $date_1 = date('Ymd', strtotime("-24 months")); 
    //Today's date
    $date_2 = date('Ymd', strtotime("now"));
    ?>		
    <?php
    $recent_args = array(
    'post_type'		=> 'event',
    'meta_query'	=> array(
    	'relation'	=> 'OR',
    	// check to see if end date has been set
    	array(
    	'key'		=> 'end_date',
    	'compare'	=> 'BETWEEN',
    	'type'		=> 'numeric',
    	'value'		=> array($date_1, $date_2),
    	),
    	// if no end date has been set use start date
    	array(
    	'key'		=> 'date',
    	'compare'	=> 'BETWEEN',
    	'type'		=> 'numeric',
    	'value'		=> array($date_1, $date_2),
    	)
    ),
    'orderby'	=> 'meta_value_num',
    'order'		=> 'DESC',
    'nopaging'	=> true
    );
    ?>
    <?php 
    // the recent events query
    $recent_query = new WP_Query( $recent_args ); 
    ?>
    <?php if ( $recent_query->have_posts() ) : ?>
    <?php while ( $recent_query->have_posts() ) : $recent_query->the_post(); ?>
    
    <!-- do stuff -->
    
    <?php endwhile; ?>
    <?php wp_reset_postdata(); ?>
    <?php else:  ?>
    <p><?php _e( 'There are currently no recent events to list, please check back soon.' ); ?></p>
    <?php endif; ?>

    Any help would be very much appreciated. Perhaps this is more of a WP question than an ACF one although I am guessing the date picker field is useful to many for precisely this kind of thing.

  • Under this section https://codex.wordpress.org/Class_Reference/WP_Query#Custom_Field_Parameters look for the nested meta query example, it’s the last code example in the section. Basically you need to do the query you’re doing in a nested query with the relation of ‘OR’ and the add another query at the top level that does and ‘AND’ to make sure the end date is < today.

    Hope that helps.

  • thanks john.

    so if i read you correctly i should end up with something that looks a bit like this?

    <h4>Recent Events</h4>
    <?php 
    //Set server timezone to GMT
    date_default_timezone_set('Europe/London'); 
    //Past cutoff date - the arg will look between today's date and this future date to see if the post fall within the 2 dates
    $date_1 = date('Ymd', strtotime("-24 months")); 
    //Today's date 
    $date_2 = date('Ymd', strtotime("now"));
    ?>		
    <?php
    $recent_args = array(
    'post_type'  => 'event',
    'meta_query' => array(
    'relation' => 'AND',
    array(
    'key'     => 'end_date',
    'value'   => $date_2,
    'compare' => '<',
    ),
    array(
    'key'		=> 'end_date',
    'compare' 	=> 'BETWEEN',
    'type' 		=> 'DATE',
    'value' 	=> array($date_1, $date_2),
    ),
    // if no end date has been set use start date
    array(
    'key'       => 'date',
    'compare' 	=> 'BETWEEN',
    'type' 		=> 'DATE',
    'value' 	=> array($date_1, $date_2),
    ),
    ),
    'orderby' 		=> 'meta_value_num',
    'order'        	=> 'DESC',
    'nopaging'     	=> true
    );
    ?>
    <?php 
    // the recent events query
    $recent_query = new WP_Query( $recent_args ); 
    ?>
    <?php if ( $recent_query->have_posts() ) : ?>
    <?php while ( $recent_query->have_posts() ) : $recent_query->the_post(); ?>

    the problem is that the first AND excludes any events in the past which do not have an end date at all (this is likely to be most of them). the above is working fine for events which have both start and end determined now but i need also to include events that only have one date, as in the first feed on the page.

    you can see what i mean here: http://wpa.matmartin.co.uk/events/ – there is a one-day event in the system which should be appearing in that recent list, too.

  • More like this

    
    <?php
    $recent_args = array(
      'post_type'  => 'event',
      'meta_query' => array(
        'relation' => 'AND',
        array(
          'key'     => 'end_date',
          'value'   => $date_2,
          'compare' => '<',
        ),
        array(
          'relation' => 'OR',
          array(
            'key'    => 'end_date',
            'compare'   => 'BETWEEN',
            'type'     => 'DATE',
            'value'   => array($date_1, $date_2),
          ),
          // if no end date has been set use start date
          array(
          'key'       => 'date',
          'compare'   => 'BETWEEN',
          'type'     => 'DATE',
          'value'   => array($date_1, $date_2),
          ),
        ),
      ),
      'orderby'     => 'meta_value_num',
      'order'          => 'DESC',
      'nopaging'       => true
    );
    ?>
    
  • Giving it a little more thought, this should handle where the end date is not set, I think.

    
    <?php
    $recent_args = array(
      'post_type'  => 'event',
      'meta_query' => array(
        'relation' => 'AND',
        array(
          'relation' => 'OR',
          array(
            'key'     => 'end_date',
            'value'   => $date_2,
            'compare' => '<',
          ),
          array(
            'key'     => 'end_date',
            'compare' => 'NOT EXISTS',
          ),
        ),
        array(
          'relation' => 'OR',
          array(
            'key'    => 'end_date',
            'compare'   => 'BETWEEN',
            'type'     => 'DATE',
            'value'   => array($date_1, $date_2),
          ),
          // if no end date has been set use start date
          array(
          'key'       => 'date',
          'compare'   => 'BETWEEN',
          'type'     => 'DATE',
          'value'   => array($date_1, $date_2),
          ),
        ),
      ),
      'orderby'     => 'meta_value_num',
      'order'          => 'DESC',
      'nopaging'       => true
    );
    ?>
    
  • John, this is spot on – thank you. I had managed to get close but not found this exact nest. I’m now off to look at it really carefully and figure out how it’s working.

    Thanks again for your help.

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

You must be logged in to reply to this topic.