Query post if any reapeater subfield is equal to value

• feyfey

• September 14, 2018 at 5:11 am

Hi all,

I want to query posts by a “breed” sub-field value which is in a repeater named “animals”. I have almost all the query I need, except one : The query to get the posts where any sub-field “breed” is equals to a value (and ideally where any sub-field “breed” is equals to an array of values). See below what’s works or not, hope you can help me.

I can query posts where “animals” containing at least a row where the sub-field “breed” is equals to “Dog” by using the “posts_where” filter :

function my_posts_where( $where ) {
$where = str_replace("meta_key = 'animals_$", "meta_key LIKE 'animals_%", $where);
return $where;
}
add_filter('posts_where', 'my_posts_where');
// args
$args = array(
'numberposts' => -1,
'post_type' => 'breeder',
'meta_query' => array(
array(
'key' => 'animals_$_breed',
'compare' => '=',
'value' => 'Dog',
)
)
);
// query
$the_query = new WP_Query( $args );

I accidentally found how to query post where “animals” is empty by using an EXISTS and empty value hack ( still not understand why it’s working, maybe you can tell me ? ) :

// args
$args = array(
'numberposts' => -1,
'post_type' => 'breeder',
'meta_query' => array(
array(
'key' => 'animals',
'compare' => 'EXISTS',
'value' => '',
)
)
);
// query
$the_query = new WP_Query( $args );

So as explained in top of my tropic, I now want to query posts where any sub-field is equal to a value. I try to used the “posts_where” filter and a “!=” compare operator but it’s works as “Animals” is not empty and “breed” not contain only the value “Dog” :

// args
$args = array(
'numberposts' => -1,
'post_type' => 'breeder',
'meta_query' => array(
array(
'key' => 'animals_$_breed',
'compare' => '!=',
'value' => 'Dog',
)
)
);
// query
$the_query = new WP_Query( $args );

I try to use the “NOT LIKE” operator, not working, it’s return no post :

// args
$args = array(
'numberposts' => -1,
'post_type' => 'breeder',
'meta_query' => array(
array(
'key' => 'animals_$_breed',
'compare' => 'NOT LIKE',
'value' => 'Dog',
)
)
);
// query
$the_query = new WP_Query( $args );

And because I ideally want to query posts where any sub-field is equal to “Dog” and “Cat” neither, I try to use the “NOT IN” operator, and as the one above, it’s return no post :

// args
$args = array(
'numberposts' => -1,
'post_type' => 'breeder',
'meta_query' => array(
array(
'key' => 'animals_$_breed',
'compare' => 'NOT IN',
'value' => array('Dog', 'Cat'),
)
)
);
// query
$the_query = new WP_Query( $args );

Does someone had a solution ? Just to be clear I had several post including at least one with no row in “animals”, one with row and “breed” not equal to “Dog” or “Cat” (like “Horse”) and one where “breed” is equal to “Dog” and/or “Cat”.

Thanks a lot,

• John Huebner

• September 14, 2018 at 9:47 am

There is an explanation of how to do this on this page https://www.advancedcustomfields.com/resources/query-posts-custom-fields/ 4. Sub custom fields values and there is a conversation here, the last comment tells you how to fix the example so that it works https://support.advancedcustomfields.com/forums/topic/percentage-in-sql/

Or you can follow my advice here https://acfextras.com/dont-query-repeaters/

• feyfey

• September 15, 2018 at 6:22 pm

Hi John,

I already use the ACF documentation, and the forum thread you listed is useful but I read your advice and I’ll change my way to do 🙂

Yes I need the repeater field because I used it 99% for only store complexe and related datas which are not used in query but fill by several staff members (and it’s very userfriendly). For the last 1% I’ll look for a simple solution based on your advices.

Thanks !

• marshall.wells

• December 16, 2020 at 11:50 am

John’s Link wasn’t working for me – seems like the server is down – found it on the wayback machine: https://web.archive.org/web/20190814230622/https://acfextras.com/dont-query-repeaters/