wordpressadvanced-custom-fieldsacfpro

Wordpress, ACF repeater, date filter


There are posts in which acf repeater field with date ranges from and to Need to filter posts that fall within the date range in any of the repeaters The filter does not work correctly.

For example post with repeater 01.06.2025 --- 15.06.2025 01.08.2025 --- 15.08.2025

Appears if the dates 20.06.25 - 30.06.25 are entered.

function blog_load_more()
{
    add_filter('posts_where', 'my_posts_where');
    function my_posts_where($where)
    {
        $where = str_replace("meta_key = 'date_ranges_$", "meta_key LIKE 'date_ranges_%", $where);

        return $where;
    }


    $user_date_from = isset($_POST['date_from']) ? sanitize_text_field($_POST['date_from']) : '';
    $user_date_to = isset($_POST['date_to']) ? sanitize_text_field($_POST['date_to']) : '';


    $args = array(
        'post_type' => 'post',
        'posts_per_page' => 6,
        'suppress_filters' => false,
        'meta_query' => [
            'relation' => 'AND',
            [
                'relation' => 'AND',
                [
                    'key' => 'date_ranges_$_date_from',
                    'value' => $user_date_to,
                    'compare' => '<=',
                    'type' => 'DATE',
                ],
                [
                    'key' => 'date_ranges_$_date_to',
                    'value' => $user_date_from,
                    'compare' => '>=',
                    'type' => 'DATE',
                ],
            ],
        ]
    );


    $ajaxposts = new WP_Query($args);

    remove_filter('posts_where', 'my_posts_where');

    $output = '';

    if ($ajaxposts->have_posts()) {
        ob_start();
        while ($ajaxposts->have_posts()) : $ajaxposts->the_post();
            echo '<div>' . get_the_title() . '</div>';
?>
            <?php
        endwhile;
        $output = ob_get_contents();
        ob_end_clean();
    }

    $result = [
        'html' => $output,
    ];

    echo json_encode($result);
    exit;
}
add_action('wp_ajax_blog_load_more', 'blog_load_more');
add_action('wp_ajax_nopriv_blog_load_more', 'blog_load_more');

Tried it, but it doesn't work as expected

'meta_query' => [
            'relation' => 'AND',
            [
                'relation' => 'AND',
                [
                    'key' => 'date_ranges_$_date_from',
                    'value' => array( $user_date_from, $user_date_to ),
                    'compare' => 'BETWEEN',
                    'type' => 'DATE',
                ],
                [
                    'key' => 'date_ranges_$_date_to',
                    'value' => array( $user_date_from, $user_date_to ),
                    'compare' => 'BETWEEN',
                    'type' => 'DATE',
                ],
            ],

Solution

  • add_action('acf/save_post', 'normalize_date_ranges_for_filtering', 20);
    function normalize_date_ranges_for_filtering($post_id) {
        if (get_post_type($post_id) !== 'post') return;
    
        delete_post_meta($post_id, 'normalized_date_range');
    
        if (have_rows('date_ranges', $post_id)) {
            while (have_rows('date_ranges', $post_id)) {
                the_row();
                $from = get_sub_field('date_from');
                $to = get_sub_field('date_to');
                if ($from && $to) {
                    add_post_meta($post_id, 'normalized_date_range', "$from|$to");
                }
            }
        }
    }
    

    I save all repeater in one field

    normalized_date_range
    

    in the post
    because due to the large number of fields meta_query is very slow.

    pulling posts that have a match.
    
        $date_from = sanitize_text_field($_POST['date_from']);
        $date_to   = sanitize_text_field($_POST['date_to']);
    
        global $wpdb;
    
        $sql = $wpdb->prepare("
            SELECT post_id FROM {$wpdb->postmeta}
            WHERE meta_key = 'normalized_date_range'
            AND (
                CAST(SUBSTRING_INDEX(meta_value, '|', 1) AS DATE) <= %s
                AND CAST(SUBSTRING_INDEX(meta_value, '|', -1) AS DATE) >= %s
            )
        ", $date_to, $date_from);
    
        $post_ids = $wpdb->get_col($sql);
    
    
    
    
    $args = array(
            'post_type' => 'post',
            'posts_per_page' => 6,
            'post__in' => $post_ids ?: [0],
    );
    
    $args = array(
            'post_type' => 'post',
            'posts_per_page' => 6,
            'has_password' => false,
            'orderby' => 'date',
            'order' => 'DESC',
            //'suppress_filters' => false,
            'post__in' => $post_ids ?: [0],
            'paged' => $paged,