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' ) )
but for a Post Object field, where the value is an integer, use:
'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 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 ) );