Query posts with dates in repeater field

• vengiss

• October 21, 2014 at 5:02 am

I have a post type called “property” where users can store properties to rent and I’m using a repeater field to allow them to set the dates in which the property isn’t available, it looks something like this:

– dates (repeater field)
– start_date (date field)
– end_date (date field)

The problem is that I need to filter available properties on the front end using two datepickers (from_date and to_date) but I’m not quite sure how to put together the query, in pseudo code it’d be something like:

select all properties where (from_date < start_date AND to_date < start_date) OR (from_date > end_date AND to_date > end_date)

Basically I need to select all properties that don’t contain the selected dates in the repeater fields.

I put together this code roughly based on http://www.advancedcustomfields.com/resources/how-to-query-posts-filtered-by-custom-field-values/#example-5 and http://support.advancedcustomfields.com/forums/topic/wp_query-based-comparing-two-repeater-field-values/

function jvs_filter_properties( $query ) {
    if ( ! is_admin() && $query->is_main_query() ) {
        if ( ! empty( $_GET['from'] ) && ! empty( $_GET['to'] ) ) {
            $query->set( 'meta_query',
                array(
                    'relation' => 'OR',
                    array(
                        'relation' => 'AND',
                        array(
                            'key'     => 'dates_%_start_date',
                            'value'   => $_GET['from'],
                            'type'    => 'DATE',
                            'compare' => '<'
                        ),
                        array(
                            'key'     => 'dates_%_start_date',
                            'value'   => $_GET['to'],
                            'type'    => 'DATE',
                            'compare' => '<'
                        )
                    ),
                    array(
                        'relation' => 'AND',
                        array(
                            'key'     => 'dates_%_end_date',
                            'value'   => $_GET['from'],
                            'type'    => 'DATE',
                            'compare' => '>'
                        ),
                        array(
                            'key'     => 'dates_%_end_date',
                            'value'   => $_GET['to'],
                            'type'    => 'DATE',
                            'compare' => '>'
                        )
                    )
                )
            );

            $query->set( 'suppress_filters', false );

        }
    }
    return $query;
}
add_action( 'pre_get_posts', 'jvs_filter_properties' );

function acf_posts_where( $where ) {

    $where = str_replace( "meta_key = 'dates_%", "meta_key LIKE 'dates_%", $where );

    return $where;

}
add_filter( 'posts_where', 'acf_posts_where' );

But it doesn’t work as expected, I’m not sure if the relations are set correctly, according to this post: http://stackoverflow.com/questions/18401879/meta-query-how-to-search-using-both-relation-or-and it should work.

• Aminta

• April 3, 2017 at 8:24 am

Hi @vengiss , please follow this thread