Reverse Query Relationship subfield which is nested in a Repeater Field

• EugeneNyawara

• February 6, 2014 at 2:48 am

I asked this on a different post a long time ago, I’ve never been able to figure it out.

I have post-type A and another post-type B. Post type A posts have repeater fields which contain a relationship field relating to post-type B. On Post type B’s single.php I need to query all the post-type A posts that have been related with it.

According to the Documentation Here, this is how the normal code to do this would look if the Relationship field was a Main field and not a Subfield:

$doctors = get_posts(array(
	'post_type' => 'whose-posts-we-want-to-display',
	'meta_query' => array(
		array(
			'key' => 'location', // name of custom field
			'value' => '"' . get_the_ID() . '"',
			'compare' => 'LIKE'
		)
	)
));

My issue is with 'key' => 'location', // name of custom field. How do I have this work as a subfield?

I’m mostly a Front-end Dev. Sample code would be most appreciated 🙂

• Elliot

• February 7, 2014 at 10:12 am

Hi @EugeneNyawara

This is a great question, and one which can be achieved by reading about querying posts via a sub field value here:
http://www.advancedcustomfields.com/resources/how-to/how-to-query-posts-filtered-by-custom-field-values/#example-5

I have done some copy / paste and put together a template for you. Just replace {$repeater_field_name} with your repeater field name, and {$sub_field_name} with your sub field name, and it should work! Fingers crossed:

<?php 
 
// custom filter to replace '=' with 'LIKE'
function my_posts_where( $where )
{
	$where = str_replace("meta_key = '{$repeater_field_name}_%_{$sub_field_name}'", "meta_key LIKE '{$repeater_field_name}_%_{$sub_field_name}'", $where);
 
	return $where;
}
 
add_filter('posts_where', 'my_posts_where');
 
// args
$args = array(
	'post_type'	=> 'whose-posts-we-want-to-display',
	'meta_query' => array(
		array(
			'key' => '{$repeater_field_name}_%_{$sub_field_name}',
			'value' => '"' . get_the_ID() . '"',
			'compare' => 'LIKE'
		)
	)
);
 
// get results
$the_query = new WP_Query( $args );
 
// The Loop
?>
<?php if( $the_query->have_posts() ): ?>
	<ul>
	<?php while ( $the_query->have_posts() ) : $the_query->the_post(); ?>
		<li>
			<a href="<?php the_permalink(); ?>"><?php the_title(); ?></a>
		</li>
	<?php endwhile; ?>
	</ul>
<?php endif; ?>
 
<?php wp_reset_query();  // Restore global post data stomped by the_post(). ?>

Thanks
E

• cinq75

• February 22, 2014 at 9:43 am

Hi,

Almost what I need but struggling to get last details.

I have a music festival site, we have artists who play diffrent venues at diffrent times. The artists custom post has a repeater that selects venue and time.

On the venue page we can pull the correct artists but we cant get the date/time to appear. Nearest I get is all the listings.

Basically I need to show a reverse lookup with venue and display the times subfield as well.

Pulling my hair out at the momment …

Thanks

B

• Elliot

• February 24, 2014 at 10:46 am

Hi @cinq75

This is getting quite advanced and I am unable to provide support for this custom query.

Please debug the SQL which is being generated by your WP_Query object. This will show you where any SQL logic errors are occurring.

Thanks
E

• shahab

• January 25, 2015 at 7:30 pm

Hi Elliot,

I am looking to solve a similar problem. However, instead of a post relationship field, I need to look for USERS who were added inside of a repeater field.

Basically, I have a post type “meeting”. Under each meeting, there is a repeater field “staff members” where I add “users” to a meeting.

When users log-in, they should be able to see what meetings they have been assigned to. I see that you have used ‘value’ => ‘”‘ . get_the_ID() . ‘”‘. How would we go on about doing the same thing when we are dealing with user relationship field?

Thanks.

• shahab

• January 25, 2015 at 8:21 pm

Ok,

I was able to find a solution. Its what Elliot has suggested but using $wpdb. For some reason, Elliot’s meta query example did not work for me (returns no results).

Here is what I did:

global $wpdb;
$meetings = $wpdb->get_results( "SELECT post_id FROM wp_postmeta WHERE meta_key LIKE 'members_%_staff' AND meta_value = '{$user_id}'", OBJECT );

In the above example:

• “members” is the name of the repeater field
• “staff” is the name of the subfield (a user in my case)
• “{$user_id}” is the ID of the user which we need to lookup in meetings

$meetings contains all the post ids that have the staff (user) added as a subfield.

Like Elliot mentioned, the “%” is important as we don’t know the row index of the subfield.

$wpdb packs so much power than your standard wordpress $args query model. Hope that helps someone 🙂

• shahab

• January 25, 2015 at 8:49 pm

cinq75,

Have a look at my post about $wpdb. This might help you as well. Do something like this:

First, get all post ids of bands for a particular venue id, like:

global $wpdb;
$bands = $wpdb->get_results( "SELECT * FROM wp_postmeta WHERE meta_key LIKE 'venuerepeaterfieldname_%_band' AND meta_value = '{$venue_id}'", OBJECT );

The $bands object will contain the band post ids and also the row number which is the order in which it lies in the repeater field . The repeater row index is important information because you can use that to get all other fields in the same row of that repeater field.

For example, if you band added to the venue as the 3rd row of the repeater field, you will have that information in the $bands object array as “venuerepeaterfieldname_3_band”. You can then do something like:

$band_index_array = explode("_","venuerepeaterfieldname_3_band");
$band_index = $row_index_array[1]; //equal to 3

Then simply get all the values on that repeater row, using:

$bands = get_field('venuerepeaterfieldname', $venue_id);
$concerned_band = $bands[$band_index];

$concerned_band array will contain all fields that you may need. Hope that helps.

• ladyheatherly

• October 1, 2015 at 9:48 am

How would you reverse query a Post Object (post_object) subfield which is nested in a repeater field? Same problem as EugeneNyawara, just with the Post Object field.

• extrasmall

• November 12, 2017 at 3:51 pm

For anyone that runs across this, @elliot’s custom filter to allow the LIKE function to work no longer works as of WP 4.8.3

If you replace it with the following function you’ll be all set. Hopefully this saves someone else the few hours I lost on it 🙂

function allow_wildcards( $where ) {
global $wpdb;
$where = str_replace(
"meta_key = '{$repeater_field_name}_%_{$sub_field_name}", 
"meta_key LIKE '{$repeater_field_name}_%_{$sub_field_name}",
$wpdb->remove_placeholder_escape($where)
);
return $where;
}

add_filter('posts_where', 'allow_wildcards');

• aingham

• November 21, 2017 at 9:13 am

@extrasmall Thanks for the above code! I got as far as understanding why the str_replace wasn’t working, but didn’t know where the {……..} in the query was coming from.

• Michelle

• December 5, 2017 at 6:54 am

Thanks so much @extrasmall you did indeed save me hours with this! 🙂

• FloatingPoint

• December 14, 2020 at 10:41 pm

Anyone struggling with the above query from @extrasmall the working syntax should be (a couple of quote marks missing on the str_replace statement)

function allow_wildcards( $where ) {
global $wpdb;
$where = str_replace(
"meta_key = '{$repeater_field_name}_%_{$sub_field_name}'", 
"meta_key LIKE '{$repeater_field_name}_%_{$sub_field_name}'",
$wpdb->remove_placeholder_escape($where)
);
return $where;
}

add_filter('posts_where', 'allow_wildcards');