Sorting using ACF Date field

• tsering

• August 11, 2021 at 6:44 pm

Hi at all,

I’ve got a problem with sorting events with ACF date fieldand I hope someone can help me.

The issue i am having is that some events run over a couple of days. But I’d like that event to remain visible until the event end date has passed. Right now, once the event start date has passed, the event moves to the bottom of my event list. However, I’d like it to stay in the same chronological order of start date until the end date is over.

I would like to get the display order as below.

1. ONGOING EVENTS
2. UPCOMMING EVENTS
3. PAST EVENTS

I have tried all solutions I can think of, but nothing doesn’t work.
Here’s my code…

		$now = date('Y-m-d H:i:s');

		$meta_query = array(

			'meta_query'   => array(

				'relation'      => 'OR',

				array(

					'relation' => 'OR',

					'event_start' => array(
						'key'       => 'start_date',
						'compare'   => '<=',
						'value'     => $now,
						'type'      => 'DATETIME'
					),

					'event_end'   => array(
						'key'       => 'end_date',
						'compare'   => '>=',
						'value'     => $now,
						'type'      => 'DATETIME'
					),

				),

				array(

					'relation'     => 'OR',

					'event_upcoming' => array(
						'key'       => 'start_date',
						'compare'   => '>=',
						'value'     => $now,
						'type'      => 'DATETIME'
					)
				),

				array(

					'relation' => 'AND',

					'event_past'  => array(
						'key'     => 'start_date',
						'compare' => '<',
						'value'   => $now,
						'type'    => 'DATETIME',
					),
				),

			),

		);
		$query->set('meta_query', $meta_query);

		$query->set(
			'orderby',
			array(
				'event_start' => 'DESC',
				'event_end' => 'DESC',
				'event_upcoming' => 'DESC',
				'event_past' => 'DESC',
			)
		);

		$query->set('order', 'DESC');

Any help would be so much appreciated.

Regards!
Tsering

• John Huebner

• August 12, 2021 at 11:49 am

You will not be able to get the order that you want.

you are first ordering events by start
event_start' => 'DESC',
then by end
'event_end' => 'DESC',
the following two orderby values will have not effect because you are trying to order by the same values that have already been used to order the values.

1 and 2 are not an issue, but placing past events after upcoming events will be impossible using WP_Query.

• tsering

• August 12, 2021 at 12:41 pm

Thank you very much for your answer.
Eventually I’m done with the code below:

$now = date('Y-m-d H:i:s');

        $meta_query = array(

            'meta_query'   => array(

                'relation' => 'OR',

                array(
                    'event_start' => array(
                        'key'     => 'start_date',
                        'compare' => '<',
                        'value'   => $now,
                        'type'    => 'DATETIME'
                    ),

                    'event_end'   => array(
                        'key'     => 'end_date',
                        'compare' => '>',
                        'value'   => $now,
                        'type'    => 'DATETIME'
                    ),
                ),

                array(
                   'event_upcoming' => array(
                        'key'       => 'start_date',
                        'compare'   => '>=',
                        'value'     => $now,
                        'type'      => 'DATETIME'
                    )
                ),

                array(

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

            ),

        );
        $query->set('meta_query', $meta_query);

        $query->set(
            'orderby',
            array(
                'event_start' => 'DESC',
                'event_end' => 'DESC',
                'event_upcoming' => 'ASC',
                'event_past' => 'DESC',
            )
        );

$query->set('order', 'DESC');

And I was able to get that order:
1. ONGOING EVENTS
2. PAST EVENTS
3. UPCOMMING EVENTS

Is it not possible to use WP_Query for PAST EVENTS to be last?

Are there any other ways?

• John Huebner

• August 12, 2021 at 1:06 pm

To be honest, I can’t even figure out how you’re getting past events to show up between current and upcoming events. The only way this will work is if the start date past event is after the start date of the current event.

• John Huebner

• August 12, 2021 at 1:14 pm

Add a “Past” event that both starts and ends before the “Current” (ongoing) event an I think that you’ll find that it appears before the “Ongoing” events.

• tsering

• August 12, 2021 at 3:52 pm

John, thank you for the tips, but I think my knowledge is too little. Is it more or less like this?

$now = date('Y-m-d H:i:s');

	$meta_query = array(

		'meta_query'   => array(

			'relation'      => 'OR',

				array(
					'event_start' => array(
						'key'       => 'start_date',
						'compare'   => '<',
						'value'     => $now,
						'type'      => 'DATETIME'
					),
					'event_end'   => array(
						'key'       => 'end_date',
						'compare'   => '>',
						'value'     => $now,
						'type'      => 'DATETIME'
					),
				),

				array(
					'event_upcoming' => array(
						'key'       => 'start_date',
						'compare'   => '>=',
						'value'     => $now,
						'type'      => 'DATETIME'
					)
				),

				array(
					'event_past' => array(
						'key'       => 'end_date',
						'compare'   => '<',
						'value'     => $now,
						'type'      => 'DATETIME'
					),
				),
			),
		);

		$query->set('meta_query', $meta_query);

		$query->set(
			'orderby',
			array(
				'event_start' => 'DESC',
				'event_end' => 'DESC',
				'event_upcoming' => 'DESC',
				'event_past' => 'ASC',
			)
		);

		$query->set('order', 'ASC');

I have no ideas anymore and I am broken 🙁

• John Huebner

• August 12, 2021 at 4:30 pm

You can only order the posts a specific field once.

The only way you can accomplish what you want to do, having past events after current future events would be to do 2 queries.

// current and future
$meta_query = array(
  'relation' => 'OR',
  'event_start' => array(
    'key' => 'start_date',
    'value' => $now,
    'compare' => '>=',
    'type' => 'DATETIME'
  ),
  'event_end' => array(
    'key' => 'end_date',
    'value' => $now,
    'compare' => '>=',
    'type' => 'DATETIME'
  )
);
$orderby = array(
  'event_start' => 'ASC',
  'event_end' => 'ASC'
);//

// past events
$meta_query = array(
  'event_end' => array(
    'key' => 'end_date',
    'value' => $now,
    'compare' => '<',
    'type' => 'DATETIME'
  )
);
$orderby = array(
  'event_end' => 'ASC'
)

I’m not sure how else to explain this. “start_date” and “end_date” is each one field. It does not matter how many clauses you add to the meta query, the posts can only be ordered by the field once. Your query is basically returning ALL event posts, no matter what the dates are and ordering them by the first 2 clauses in your orderby. The second 2 clauses have no effect because the posts have already been ordered by the fields that they refer to.

In the end you cannot get the order that you want based on a single query and to be quire honest, I do not believe that what you want can be achieved without a significant amount of work.

There is one way that I can see this being done but it means that your main query would need to do 3 queries. I am assuming this is on the main query and in a pre_get_posts filter because you are using query->set()

// $post__in will collect the post IDs in the order to display
$post__in = array();

// query all current and future posts
// returning only an array of ID values
$current_future_args = array(
  'post_type' => 'event',
  'posts_per_page' => -1,
  'fields' => 'ids',
  'meta_query' => array(
    'relation' => 'OR',
    'event_start' => array(
      'key' => 'start_date',
      'value' => $now,
      'compare' => '>=',
      'type' => 'DATETIME'
    ),
    'event_end' => array(
      'key' => 'end_date',
      'value' => $now,
      'compare' => '>=',
      'type' => 'DATETIME'
    )
  ),
  // order current => future posts how you want them
  'orderby' => array(
    'event_start' => 'ASC',
    'event_end' => 'ASC'
  )
);
$current_future = new WP_Query($current_future_args);
if (count($current_future_args->posts)) {
  $post__in = $current_future_args->posts;
}

// query past events returning IDs
$past_args = array(
  'post_type' => 'event',
  'posts_per_page' => -1,
  'fields' => 'ids',
  'meta_query' => array(
    'relation' => 'OR',
    'event_start' => array(
      'key' => 'start_date',
      'compare' => 'EXISTS'
    ),
    'event_end' => array(
      'key' => 'end_date',
      'value' => $now,
      'compare' => '<',
      'type' => 'DATETIME'
    )
  ),
  // order past posts how you want them
  'orderby' => array(
    'event_start' => 'DESC',
    'event_end' => 'DESC'
  )
);
$past = new WP_Query($past_args);
if (count($past->posts)) {
  // mergs past events in post__in
  $post__in = array_merge($post__in, $past->posts);
}

// set post__in and orderby for main query
if (!empty($post__in)) {
  $query->set('post__in', $post__in);
  $quert->set('orderby', 'post__in');
}

• tsering

• August 12, 2021 at 6:02 pm

Thank you. I know more now.

But I don’t know where to add it.
To the main query (archive-event.php) or in the pre_get_posts filter?

• John Huebner

• August 13, 2021 at 8:39 am

I was assuming from your code that you were using a pre_get_posts filter and that is where this code would be added if I used it.

Honestly, I would probably not use it, it means that your main query will require 3 queries and this will reduce performance. I would likely just show current and future events archive page and then build a separate page for showing past events. I would only do this if the client insisted and first I would explain that there was a potential for this slowing down the page load and make sure they understood that before I did it.

• tsering

• August 13, 2021 at 10:12 am

Thank you for your commitment.

Yes, I am using a pre_get_posts filter (functions.php) and archive-event.php (with standard loop to display posts).

I understand that performance is very important, but I cannot display the pages separately because I am using the FacetWP plugin.

And now I don’t know what to do anymore 🙁

• John Huebner

• August 13, 2021 at 10:20 am

Another way to do this.

Create another field, this field does not have to be an ACF field. It will be a true/false field of sorts holding either a 1 or a 0. This flag will indicate if the event dates have passed.

Create a CRON (https://developer.wordpress.org/plugins/cron/), there may be plugins that let you do this. That queries events where the date has passed and sets this flag to 1.

Order posts by this field first, then the start and end date.

• tsering

• August 13, 2021 at 12:39 pm

John, thank you for all your help.

Unfortunately, all this is too difficult for me.

Thank you once again 🙂