Example for custom WP_Query, some problems with

Hi Patrick,

Could you please provide more context to your example? It looks like this has nothing to do with the ACF RRule field.

This example assumes that you have an events post type that has a start_date field and an end_date field. The query is supposed to retrieve all events starting before today and ending in more than a month. This query doesn’t output anything for one of the following reasons:

• You don’t have an events post type
• You don’t have the start_date and/or end_date fields
• You don’t have any events in your database that fulfill the condition above

Hi Marc,

very nice of you to take the time to answer my question.

For my example. I have a custom post type ‘events’ and use ACF RRule Field to assign multiple (recurring) days to an event.

So I thought your WP_Query example I posted above would do just that. In one of your posts I understood that ACF RRule Field automatically sets start_date and end_date. Or not?

Do you perhaps have a specific example that could help me here?

• This reply was modified 1 year, 7 months ago by Patte.

I indeed wrote that in another thread but that was actually a mistake and I’m very sorry for the confusion. My bad!

The start_date and end_date are actually saved as attributes of the array that is returned when you call get_field on your ACF RRule field. You can’t query them directly but what you can do is adding on action on the acf/save_post hook that set these dates as separate post meta. Here is a simplified version of what I suggested in the other post:

/**
 * Custom actions when an event is saved.
 *
 * @param  int  $post_id
 * @return void
 */
function my_post_saved($post_id)
{
    if (!$post_id || get_post_type($post_id) !== 'event') {
        return;
    }

    $rrule = get_field('rrule', $post_id);

    // Update start and end dates in post meta
    update_post_meta($post_id, 'start_date', $rrule['start_date']);
    update_post_meta($post_id, 'end_date', $rrule['end_date']);
}
add_action('acf/save_post', 'my_post_saved');

You’re going to have to save your posts in order to fire these actions.

Then your post will have two new post meta that you can query or retrieve with get_field('start_date') and get_field('end_date')

Thanks for the clarification, then I understand the whole thing a little better now ?? The Internet forgets nothing – and Google had brought me there on the wrong track. No problem.

Since I want to list every instance of the repeating event – doesn’t the meta_query have to run better over date than over the start_date?

After all, the new post meta ‘start_date’ is only present once – but my event repeats twice in my example, which shows up in the rrule array.

[date] => 2023-05-11
 [date] => 2023-05-12 

How do I get each of these [date] values into the post meta to run the meta_query after that key?

After all, I want to display each of the dates. Or am I still not understanding something here…. ?

Thanks again for your help!

Array ( [rrule] => FREQ=DAILY;COUNT=2;DTSTART=20230511T000000;INTERVAL=1 [start_date] => 20230511 [start_time] => 00:00:00 [frequency] => DAILY [interval] => 1 [weekdays] => Array ( ) [monthdays] => Array ( ) [months] => Array ( ) [monthly_by] => monthdays [bysetpos] => Array ( ) [byweekday] => Array ( ) [end_type] => count [end_date] => 20230512 [occurrence_count] => 2 [dates_collection] => Array ( [0] => DateTime Object ( [date] => 2023-05-11 00:00:00.000000 [timezone_type] => 3 [timezone] => UTC ) [1] => DateTime Object ( [date] => 2023-05-12 00:00:00.000000 [timezone_type] => 3 [timezone] => UTC ) ) [text] => t?glich 2 Mal )

Hi Patrick,

Sorry I’ve been very busy lately and could not find the time to answer.

You can’t query the database over every date of the repeating event because the dates are never actually stored in the database.

The solution I found was to set the first and last dates of the recurrence automatically when a post is saved so I can filter the posts later. Displaying all posts in an agenda requires some kind of pagination as you will never show the complete list of dates all at once. That could be hundreds or thousands of dates. What you will do is displaying all the events between two dates, for instance between today and next month.

The first step is to query all events having the first occurrence before today and the last one after the end date (a month after in our example)

Then you will have to loop through these events to get each individual date and create a new array that will contain every single occurrence sorted by dates.

Here is a simplified part of the code I wrote for my use case. I wrote some comments in it for you. I hope it will be clear enough to help you ??

Basically there are two functions:

• The first one returns a sorted array of all occurrences between two dates
• The second one is used to retrieve all dates for a specified event

<?php

/**
 * Get all events between two dates.
 *
 * @param  mixed $start
 * @param  mixed $end
 * @return array
 */
function fbdm_get_events($start = null, $end = null, $data = [])
{
    $events = [];
    $has_more = false;
    $index = 0;

    if (! $start) {
        // Defaults to today
        $start = new DateTime();
    } elseif (! $start instanceof DateTime) {
        $start = DateTime::createFromFormat('Y-m-d', $start);
    }

    if (! $end) {
        // Defaults to start date + 1 month
        $end = clone $start;
        $end->add(new DateInterval('P1M'));
    } elseif (! $end instanceof DateTime) {
        $end = DateTime::createFromFormat('Y-m-d', $end);
    }

    $start->setTime(0,0,0);
    $end->setTime(0,0,0);

    // Break the loop after 14 events
    while (sizeof($events) <= 14) {
        if ($index > 0) {
            $start = clone $end;
            $start->add(new DateInterval('P1D'));

            $end = clone $start;
            $end->add(new DateInterval('P1M'));
        }

        // Query all events which have not ended at start date
        $args = [
            'post_type' => 'event',
            'posts_per_page' => -1,
            'post_status' => 'publish',
            'meta_query' => [
                'relation' => 'AND',
                [
                    'key' => 'end_date',
                    'compare' => '>=',
                    'value' => $start->format('Y-m-d'),
                    'type' => 'DATE',
                ],
            ],
        ];

        if (isset($data['category'])) {
            $args['tax_query'] = [
                'relation' => 'AND',
                [
                    'taxonomy' => 'event_category',
                    'terms' => $data['category'],
                ],
            ];
        }

        if (isset($data['free']) && $data['free'] == true) {
            $args['meta_query'][] = [
                'relation' => 'OR',
                [
                    'key' => 'pricing',
                    'value' => 'free',
                ],
            ];
        }

        // Here you can add more filters if needed

        $query = new WP_Query($args);

        // Count posts from start date
        if (! count($query->get_posts())) {
            $has_more = false;
            break;
        }

        // Add end date to query arguments
        $args['meta_query'][] = [
            'key' => 'start_date',
            'compare' => '<=',
            'value' => $end->format('Y-m-d'),
            'type' => 'DATE',
        ];

        $query = new WP_Query($args);

        foreach ($query->get_posts() as $post) {
            $post->dates = fbdm_get_event_dates($post->ID);

            foreach ($post->dates as $date => $array) {
                $datetime = new DateTime($date);

                if ($datetime < $start) {
                    continue;
                } elseif ($end && $datetime > $end) {
                    $has_more = true;
                    break;
                }

                // Push the event to the array of dates
                if (! array_key_exists($date, $events)) {
                    $timestamp = $datetime->getTimestamp() + $datetime->getOffset();

                    $day = date_i18n("l j F", $timestamp);

                    $events[$date] = [
                        'day' => $day,
                        'month' => date_i18n("F Y", $timestamp),
                        'events' => [],
                    ];
                }

                foreach ($array as $time) {
                    $time_full = $time['start_time'];

                    if ($time['end_time']) {
                        $time_full .= "-{$time['end_time']}";
                    }

                    // Update an existing row
                    if (array_key_exists($post->ID, $events[$date]['events'])) {
                        $events[$date]['events'][$post->ID]['time'][] = $time_full;

                        sort($events[$date]['events'][$post->ID]['time']);
                    }
                    // Create a new row
                    else {
                        $events[$date]['events'][$post->ID] = [
                            'post' => $post,
                            'start_time' => $time['start_time'],
                            'end_time' => $time['end_time'],
                            'time' => [$time_full],
                        ];
                    }
                }

                // Sort events by start time
                usort($events[$date]['events'], function($a, $b) {
                    return $a['start_time'] <=> $b['start_time'];
                });
            }
        }

        if ($index++ > 3) {
            break;
        }
    }

    // Sort events array by date (key)
    ksort($events);

    return [
        'events' => $events,
        'has_more' => $has_more
    ];
}

/**
 * Get all dates for an event.
 *
 * @param  int $post_id
 * @return array
 */
function fbdm_get_event_dates( $post_id )
{
    if (! $post_id || get_post_type($post_id) !== 'event') {
        return;
    }

    $dates = [];

    $recurrence = get_field('recurrence');

    foreach ($recurrence['dates_collection'] as $date) {
        if (! array_key_exists($date->format('Y-m-d'), $dates)) {
            $dates[$date->format('Y-m-d')] = [];
        }

        // I created two ACF time fields along with the RRule field 
        // to set the start and end times of each occurrence.
        // If you don't need to specify the times for your occurrences
        // you can just push the date to the array instead of using key => value
        $dates[$date->format('Y-m-d')][] = [
            'start_time' => get_field('recurrence_start_time'),
            'end_time' => get_field('recurrence_end_time'),
        ];
    }

    // Sort dates
    ksort($dates);

    return $dates;
}

Awesome that you took the time to do this. Unfortunately, I will not get to test this until next week because of my vacation.