ACF Query exclude date between 2 datepickers group inside repeater

Question

this is driving me crazy. i'll explain the situation. I have a custom-post called "affittiestivi" Using ACF i created a Repeater with a group inside it, the group is composed by 2 datepicker fields to let the client marks periods where the house is rented (For example from 2021/04/10 to 2021/04/20 (All dates are stored in "Y-m-d" format for easier query to the DB) repeater name is "prenotazioni" group name is "prenotazione" the 2 datepicker fields are called "inizio" and "fine"

Users can search a check-in and check-out date (2 simple html date input) Then i need to query every post available in that period of time, if an house is booked from 2021/04/10 to 2021/04/20 and i search 2021/04/11 and 2021/04/19 it should not display. if i search between 2021/04/21 and 2021/04/30 the post should display. I'm trying to meta query but no matter what, the post will not display.

To summazire:
Repeatear name: "Prenotazioni"
Group name (Inside repeater): "Prenotazione"
Datepicker subfield 1 : "inizio"
Datepicker subfield 2 : "fine"

Any help would be much appreciated, and sorry for my english
What i have so far

/* Template Name: Ricerca Affitti */
<?php get_header();
 
$stype = $_GET['type'];
$searched_arr = $_GET['startdate'];
$searched_leave = $_GET['endate'];
 
$args = array(
     'post_type' => 'affittiestivi',
          'posts_per_page' => -1,
    );
$immobili = new WP_Query( $args ); //This query i't just to acces subfields value,
 //i know it's not the best practice tho and i'm not even sure i need this at this point
?>
 
<?php if($immobili->have_posts()) : while ($immobili->have_posts()) : $immobili->the_post(); ?>
<?php if( have_rows('prenotazioni') ): ?>
<?php while( have_rows('prenotazioni') ): the_row(); 
$data = get_sub_field('prenotazione');
$booked_from = $data['inizio'];
$booked_to = $data['fine'];
//This is working fine, var_dumped everything and they return yy-mm-dd, same format as the user input
 
$s_args = array(
    'post_type' => 'affittiestivi',
    'post_status' => 'publish',
    'posts_per_page' => -1,
    'order' => 'DESC',
    'meta_query' => array(
       'relation' => 'AND',
            array(
                'key' => 'prenotazione_inizio', //Is this the right way??
                'compare' => '>=',
                'value' => $searched_arr
            ),
             array(
                'key' => 'prenotazione_fine',
                'compare' => '<=',
                'value' => $searched_leave
            ),
    
          )
    );
endwhile;
endif;
//end acf row loop
endwhile;
endif;
//end custom Loop
$result = new WP_Query( $s_args );
if($result->have_posts()) : while ($result->have_posts()) : $result->the_post(); ?>

 //This query is always empty

<h1><?php the_title();?></h1>
 
<?php endwhile;
endif;
wp_reset_postdata();
get_footer()?>

EDIT Since i was having 0 progress i tried to see what the problem was, if the query in itself or acf fields, so i completely remove the "repeater" field and for the sake of testing i switched to a simple "int" field called "test", this query is now working and returning posts with the "test" field value >= 4

    'post_type' => 'affittiestivi',
    'post_status' => 'publish',
        'meta_query' => array(
        'relation' => 'AND', // includes ACF stuff
            array(
        'key' => 'test',
        'value' => '4',
        'compare' => '>=',
            ),
    )
);

So now i'm 100% sure the problem was (well it still is) the "repeater field", i'm doing something wrong with the tax query "key" for the group subfield inside a repeater. What am i doing wrong?

phpdev phpdev · Accepted Answer · 2021-04-19T19:38:22

Please check the below code. You can use the below query for the date range.

$args = array(  
'post_type' => 'events',
'posts_per_page' => -1,
'meta_key' => 'start_date',
'orderby' => 'meta_value_num',
'order' => 'ASC',
'meta_query' => array(
    'relation' => 'AND',
    array(
        'key' => 'start_date',
        'value' => $today,
        'compare' => '<=',
        'type' => 'NUMERIC',
    ),
    array(
        'key' => 'end_date',
        'value' => $today,
        'compare' => '>=',
        'type' => 'NUMERIC',
    ),

));

ACF Query exclude date between 2 datepickers group inside repeater

1 Answers