Querying a repater field where a value is NOT inside any repater-entry

• chrwald

• March 19, 2017 at 6:35 am

Hi,

I found out by looking at other topics here how to meta_query a repeater-field for a given value. Now I’m trying to do the opposite: Get all posts, where a given value is not inside any repeater field. Simply setting the operator to != doesn’t work (obviously), as long there is at least one repeater field entry which isn’t the value.

Is there any way to achive this inside a single wp_query? I thought about getting all posts first and filter afterwards, but was wondering, if there is an easier way to achieve this.

Any hint would be great, even a simple “Nope, not possible” would help.

Thanks and regards, Christian

• chrwald

• March 24, 2017 at 5:58 am

Okay, here is something I found out, which is working for my use case and might be helpful as a starting point for someone in the future. There might be better and more elegant ways to do this, but at least it does the trick in some cases.

Background: I use a post_relation field (among other) inside a repeater field and want to query all posts, which do not have a relation to one or more given post ids. As an example: I query posts of a post_type ‘recipe’ and want to get all posts (=recipes) which dont have a relation to some ingredients (‘ingredient’ is a post_type as well). The repeater field is named ‘ingredients’ and the relation field inside the repeater is called ‘ingredient’.

I build my wp_query step by step: standard query_args, meta_query_args and so on. Then I have an array of unwanted recipes. If this array is set (has values) I build a subquery and simply attach it at the end of the where clause (maybe not the best spot in regards to performance?). All of this happens in a closure (also, maybe not the best way to do this?).

if ( isset( $without_ingredients ) ) {
			add_filter ('posts_where', function($where) use ($without_ingredients) {
				global $wpdb;
				$sql_in_no_repeater_extension = " AND $wpdb->prefix" . "posts.ID NOT IN (SELECT post_id FROM $wpdb->prefix" . "postmeta WHERE meta_key LIKE 'ingredients_%_ingredient' AND meta_value in (" . implode (',', $without_ingredients). "))";
				return $where . $sql_in_no_repeater_extension;
			});
		}

What the simple subquery does, is getting the post id of all posts, having one of the uwanted post (ingredient) ids inside their ‘ingredients’-repeater subfields. Then these ids get removed from the original query.

So, maybe this helps someone sometime. If anyone has a hint how to do this better, I’d realy appreciate to read them.

• John Huebner

• March 24, 2017 at 9:44 am

I gave this some thought when you first posted it, but could not thing of a way to do this then. I can’t say if the way your doing it is efficient or not, but reading it gave me an idea for the only way that it might be done using only wp_query.

First do a query to find any post that has that post ID, then do a query to get any post not in that list

// your original query args created before here
$result = new WP_Query($args);
$post__not_in = array();
if (count($result->posts)) {
  $post__not_in = wp_list_pluck($results->posts, 'ID');
}
// args for new query
$args = array(
  'post_type' => 'your-post-type',
  'posts_per_page' => -1,
  'post__not_in' => $posts__not_in
)
$final_results = new WP_Query($args);

• chrwald

• March 24, 2017 at 9:28 pm

Thanks John! Why didn’t think of this? It’s straight forward and so much easier to understand then what I did.