Advanced Custom Fields (ACF) is a great WordPress plugin for adding custom meta fields. It has a very useful relationship field that can be used to denote a connection from one post to another – importantly this is a one-way relationship. When you are on PostA you can generate a list of all the posts that it is linked to.

Going in reverse

ACF documentation highlights how a clever WP_Query can be used to do a `reverse query`, i.e. when you view PostB you can get a list of all the posts that link to PostB.

What about sub-fields

The reverse query works fine as it is for top level fields, but does not work for sub-fields within, for example, a repeater. Luckily Elliot on the ACF support forum shared some code for doing a reverse query against a sub-field.
The key is just using a LIKE for the meta query.

'meta_query' => array(
	array(
		'key' => 'fieldName', // name of custom field
		'value' => '"' . get_the_ID() . '"', // matches exaclty "123", not just 123. This prevents a match for "1234"
		'compare' => 'LIKE'
	)
)

'meta_query' => array(
	array(
		'key' => 'fieldName', // name of custom field
		'value' => get_the_ID(),
		'compare' => '='
	)
)

Latest item in repeater only

Just to get more complicated, now let’s do a reverse relationship query, against a sub-field, but only the sub-field within the latest item in the repeater…
Imagine a post type of Business that has a repeater called ‘Audit’, and within it sub-fields for ‘Audit firm’ and ‘Fee’. The ‘Audit firm’ sub-field is a relationship to another post type called Auditor. On the single Auditor pages I want to show the name of each Business who they are currently auditing, i.e., where they are the ‘Audit firm’ in the last repeater entry.

To get a list of Business post IDs we have to use a $wpdb query; the key is the use of MAX(meta_key) to get the last item in the repeater. This works because ACF names it’s repeater fields repeaterName_X_fieldName, where X is the number denoting when the item was added.

The solution

The code below is heavily based on a Stack Overflow answer from Elliot (coincidence?) with added WordPress and ACF magic and help from Luke Oatham.

$meta_key = 'audit_%_audit_firm'; // meta_key for repeater sub-field.
$meta_value = '%"'. get_the_id() . '"%'; // meta_value, wrapped for use in a LIKE.
$post_status = 'publish';
$businesses = $wpdb->get_col( $wpdb->prepare(
	"
	SELECT businessMeta.post_id // Field we want in the returned column.
	FROM $wpdb->postmeta businessMeta
	INNER JOIN
		(SELECT post_id, MAX(meta_key) AS latestAuditRepeater
		FROM $wpdb->postmeta
		WHERE meta_key LIKE '%s'
		GROUP BY post_id) groupedBusinessMeta
	ON businessMeta.post_id = groupedBusinessMeta.post_id
	AND businessMeta.meta_key = groupedBusinessMeta.latestAuditRepeater
	WHERE meta_value LIKE '%s'
	AND abMeta.post_id IN
		(SELECT ID
		FROM $wpdb->posts
		WHERE post_status = '%s')
	",
	$meta_key,
	$meta_value,
	$post_status
) );