Querying posts by custom field using advanced custom fields

Advanced Custom Fields is obviously a great plugin, but there are one or two things with it's API that could be made easier.

For example, in vanilla ACF there's no easy way to get the field object from the field name, despite what the documentation says. The get_field_object only returns partial information if queried by name - to return the full object you need to use the field key. (Side note: in ACF Pro it's a little easier as you can use the acf_get_field function which does return the full field object.)

If you're developing on a single, known site where you've created the fields yourself, that's fine. But if you're developing a theme or plugin which uses ACF, you don't know the keys and the only way to find them is quite convoluted.

Another problem is selecting posts based on the custom field values.

Single-value fields

Selecting posts based on simple, single-value fields such as "Number" or "Text" is straightforward enough. You just use the standard WP_Meta_Query fields. I won't recap these here, but you can read the documentation for details.

Example query

Say we're selecting posts based on two custom fields - email and num_articles. We want to select all posts with the email joe@bloggs.com whose number of articles is greater than 1:

$args = array(
  'post_type' => 'post',
  'meta_query' => array(
    'relation' => 'AND',
    array(
      'key' => 'email',
      'value' => 'joe@bloggs.com',
      'compare' => '='
    ),
    array(
      'key' => 'num_articles',
      'value' => 1,
      'type' => 'NUMERIC',
      'compare' => '>'
    )
  )
);

$posts = get_posts( $args );
// etc...

That's all straightforward enough, and no different to querying by custom field without ACF.

Array-based fields

However, when using array-based fields such as Checkbox or fields which allow multi-selection, it gets more complicated.

The documentation says you should use the LIKE operator for these fields, which at first glance makes sense.

Consider a checkbox field (field name: checkbox_field). Posts with this field will have a row in wp_post_meta which looks something like:

The meta_value column is the bit we're interested in. As this is an array-based field, the value is a serialized array. So in this case we have an array of length 2, which contains two values: one and three.

Example query

Using the LIKE operator to select posts based on this field might look something like this:

$args = array(
  'post_type' => 'post',
  'meta_query' => array(
    array(
      'key' => 'checkbox_field',
      'value' => 'three',
      'compare' => 'LIKE'
    )
  )
);

$posts = get_posts( $args );
// etc...

In this case, it all works fine.

Another example

However, consider a User field which allows multi-section:

Posts with this field will have a user_field value in wp_post_meta which looks something like:

So in this case, the values are stored as arrays containing user IDs.

Unexpected results

A query using this field might look like this:

$args = array(
  'post_type' => 'post',
  'meta_query' => array(
    array(
      'key' => 'user_field',
      'value' => '2',
      'compare' => 'LIKE'
    )
  )
);

$posts = get_posts( $args );
// etc...

However, this would give unexpected results. As the values are serialized arrays, the above query would match 2 posts - 123 and 125 - as both contain the string "2". The first (post_id = 123) is correct as the "2" refers to the user ID we're looking for. But the second result is picking up the serialized string reference "s:2", which we definitely don't want in the results.

Improving the query

An improved way of selecting posts based on array values needs to take this into account. The documentation for WP_Meta_Query mentions that (since 3.7) it supports SQL operators such as 'REGEXP' and 'NOT REGEXP'. This can be used to improve our field selection in the above example, to produce something like this:

$args = array(
  'post_type' => 'post',
  'meta_query' => array(
    array(
      'key' => 'user_field',
      'value' => '^2$|s:1:"2";',
      'compare' => 'REGEXP'
    )
  )
);

$posts = get_posts( $args );
// etc...

This will now return the correct number of posts as it takes the serialization format into account. We also have an alternate option in case the field is stored as a single value (e.g. if you switch the field from mutli to single selection).

To produce this query automatically from a variable field value, you could the following code:

$search_value = 'xyz'; // whatever
$field_value = sprintf( '^%1$s$|s:%2$u:"%1$s";', $search_value, strlen( $search_value ) );

$args = array(
  'post_type' => 'post',
  'meta_query' => array(
    array(
      'key' => 'user_field',
      'value' => $field_value,
      'compare' => 'REGEXP'
    )
  )
);
// etc...

You could obviously combine this with other meta queries to select posts based on multiple fields using the AND or OR operators.

Related: How to create and display ACF custom fields in WooCommerce. 

Conclusion

At the moment, this is the best method I'm aware of to search in array based fields. There's no native way in WP or in ACF, but if you know a better way please share them in the comments below. And if you have any other comments or improvements on this, let me know.