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',
],
],
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,