Query a meta key using an array of values where the database value is a string

Question

If a meta value in the WordPress user_meta table is set as a comma separated list and not a serialized array, how can we compare an array of possible values to the values in the database table?

Currently, we're trying this code, but it only works if the User's specializations key has only one value in the database; as soon as there are multiple values in the database, it is stored as a comma separated string and doesn't return any matches.

$args = array(
  'role' => 'member',
  'meta_query' => array(
    'key' => 'specializations',
    'value' => array('doctor', 'researcher'),
    'compare' => 'IN'
  )
);

$found_users = new WP_User_Query($args);

In our example, we only get a returned User object if the User's specialization meta value is either 'doctor' or 'researcher'. If the User's specializations meta value contains multiple values like doctor, researcher (or more) in the database then the query returns nothing.

Assuming we can't change how the meta values are created and stored in the first place, what's the best way to map an array of potential matches to a comma-separated string of values in the database?

The intended behaviour is that if you select both 'doctor' and 'researcher', the resulting query will return anyone matching any or or all of the supplied values for the query.

Answer 1

You could use multiple meta clauses in your WP_User_Query, along with a LIKE comparison.

Something like

$args = array(
    'role'  => 'member',
    'meta_query' => array(
        'relation' => 'OR',
        array(
            'key'     => 'specializations',
            'value'   => 'doctor',
            'compare' => 'LIKE',
        ),
        array(
            'key'     => 'specializations',
            'value'   => 'researcher',
            'compare' => 'LIKE',
        ),
    ),
);
$found_users = new WP_User_Query($args);

In reality you'd probably want to loop through your target specializations and build the set of clauses dynamically.

That said, be aware that queries using LIKE are expensive. I'd say this method is a last resort, assuming you cannot refactor the DB structure to store each specialization in its own row (serialization won't help you here), and assuming that there are too many records for you to just grab the lot and search them in your PHP.