I’m working on a WordPress site where I need to display tour custom post types on an archive page. Each tour has availability dates stored in ACF Repeater fields. I want to list only the tours available today and sort them by price.

Here is the approach I took:

1. Extract Tours Available Today:

   • I loop through the posts and check the availability dates in the ACF Repeater fields.

2. Sort Tours by Price:

   • Once the tours are filtered, I sort them by their price using a URL parameter (sort=high_price or sort=low_price).

Code Attempt:

function custom_elementor_query( $query ) {
    if ( is_archive() && $query->get('post_type') == 'tour' ) {
        $today_date = date('Ymd'); // Ymd format for today's date
        $tours = array();

        // Use the current query to fetch results
        if ($query->have_posts()) {
            while ($query->have_posts()) {
                $query->the_post();
                $post_id = get_the_ID();
                if (have_rows('available_dates', $post_id)) {
                    while (have_rows('available_dates', $post_id)) {
                        the_row();
                        $start_date = get_sub_field('start_date');
                        $end_date = get_sub_field('end_date');
                        $price = get_sub_field('price');

                        if ($today_date >= $start_date && $today_date <= $end_date) {
                            $tours[] = array(
                                'post_id' => $post_id,
                                'title' => get_the_title($post_id),
                                'start_date' => $start_date,
                                'end_date' => $end_date,
                                'price' => $price,
                                'permalink' => get_permalink($post_id)
                            );
                        }
                    }
                }
            }
            wp_reset_postdata();
        }

        if (!empty($tours)) {
            if (isset($_GET['sort']) && $_GET['sort'] == 'high_price') {
                usort($tours, function($a, $b) {
                    return $b['price'] - $a['price'];
                });
            } elseif (isset($_GET['sort']) && $_GET['sort'] == 'low_price') {
                usort($tours, function($a, $b) {
                    return $a['price'] - $b['price'];
                });
            }

            // Adjust the query with the sorted tour IDs
            $post_ids = array_column($tours, 'post_id');
            $query->set('post__in', $post_ids);
            $query->set('orderby', 'post__in');
        } else {
            // If no results, set an empty query
            $query->set('post__in', array(0));
        }
    }
}
add_action('elementor/query/my_tour_archive', 'custom_elementor_query');

Problem:

When using the above code, I’m encountering a memory exhaustion error:

Fatal error: Allowed memory size of 536870912 bytes exhausted (tried to allocate 262144 bytes) in /path/to/wp-includes/class-wp-query.php on line 1167

Question:

1. How can I efficiently sort tour posts by price based on the availability dates stored in ACF Repeater fields?
2. Are there better practices or methods to handle sorting by ACF Repeater fields in WP_Query to avoid memory exhaustion?

Database Storage Details:

The available_dates meta field is stored in the database as follows:

• ACF stores repeater fields in the wp_postmeta table.
• The repeater field itself has a meta key that stores the number of rows.
• Each sub-field of the repeater is stored as individual meta keys with the format available_dates_0_start_date, available_dates_0_end_date, available_dates_0_price, available_dates_1_start_date, and so on.

Example:

post_id | meta_key                     | meta_value
---------------------------------------------------
123     | available_dates              | 2
123     | available_dates_0_start_date | 20240701
123     | available_dates_0_end_date   | 20240707
123     | available_dates_0_price      | 1000
123     | available_dates_1_start_date | 20240708
123     | available_dates_1_end_date   | 20240714
123     | available_dates_1_price      | 1200

Goal:

My goal is to list the tour posts available today and sort them by price.