Advanced Custom Fields relationships are pretty amazing. You can easily manage relationships between virtually anything. The official ACF docs on querying relationship fields is a fantastic walkthrough showing how to handle the front-end display of basic relationships. It works by using the meta_query
to make a like
comparisons.
That unfortunately doesn’t work for complex (many to many) ACF relationship matching.
My scenario involves relating users and two custom post types. The relationships look like this:
- Process log (CPT) relates to one or more websites (CPT)
- Users relates to one more websites (CPT)
Now depending on who’s logged in I want to display all of the process logs which belong to all of the websites they have access to. Now for each user I know which website they should have access to. That might look something like $websites = array( "1234", "1236", "1237", "1240" );
. Based on the official ACF docs you might attempt something like this.
$arguments = array(
'post_type' => 'process_log',
'meta_query' => array(
array(
'key' => 'website', // name of custom field
'value' => '"1234","1236","1237","1240"',
'compare' => 'LIKE'
)
));
$process_logs = get_posts($arguments);
That’s not going to work, as the data is serialized within the database. Even if you did account for data serialization it would only match process logs which are assigned to all 4 websites in that precise order. In order to make the compare flexibility enough to match any item in the website array we can use some regular expression magic.
Complex relationship matching is possible using REGEXP
While reading the official documentation for WP_Meta_Query I noticed there’s a compare type called REGEXP. That allows a regular expression pattern within the query. The following regular expression pattern ("1234"|"1236"|"1237"|"1240")
run against the relationship field will returning records containing any of those IDs as shown here.
/*
* Query posts for a relationship value.
* This method uses the meta_query REGEXP to pattern match any IDs in array( "1234", "1236", "1237", "1240" ) to the database value a:4:{i:0;s:4:"1234";i:1;s:4:"1236";i:2;s:4:"1237";i:3;s:4:"1237";} (serialized array)
*/
$websites = array( '1234', '1236', '1237', '1237' );
$pattern = '("' . implode( '"|"', $websites ) . '")';
$arguments = array(
'post_type' => 'process_log',
'posts_per_page' => '-1',
'meta_query' => array(
array(
'key' => 'website',
'value' => $pattern,
'compare' => 'REGEXP',
),
),
);
$process_logs = get_posts( $arguments );
With a more complex pattern like ^(?=.*"1234")(?=.*"1236")(?=.*"1237")(?=.*"1240").*
it’s possible to match process logs which have all 4 websites in any order as shown here.
/*
* Query posts for a relationship value.
* This method uses the meta_query REGEXP to pattern match all IDs in array( "1234", "1236", "1237", "1240" ) to the database value a:4:{i:0;s:4:"1234";i:1;s:4:"1236";i:2;s:4:"1237";i:3;s:4:"1237";} (serialized array)
*/
$websites = array( '1234', '1236', '1237', '1240' );
$pattern = '^';
foreach ( $websites as $website_id ) {
$pattern .= '(?=.*"' . $website_id . '")';
}
$pattern .= '.*$';
$arguments = array(
'post_type' => 'process_log',
'posts_per_page' => '-1',
'meta_query' => array(
array(
'key' => 'website',
'value' => $pattern,
'compare' => 'REGEXP',
),
),
);
$process_logs = get_posts( $arguments );