1

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.

1 Answer 1

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.

4
  • Thanks, we'll try this. Gravity Forms User Registration add-on is what creates the meta key => value pairs and so we can't refactor how they store multiple checkbox values, unfortunately. Jan 30, 2023 at 21:52
  • At what point is it "too many records"? We don't know if we'll have 100 or 1000 users to search through by the time the directory is finished. We considered filtering search results with JS to offload the work to the client side, but if user data and meta data need to be sent over for hundreds or even thousands of members before filtering on the client side then that didn't seem efficient either. Jan 30, 2023 at 21:55
  • You're probably right that it's too many to pull at once. I'd say implement the LIKE version and run some tests.
    – vancoder
    Jan 30, 2023 at 22:24
  • We took your advice, ditched the existing plugin, and went with something where we had more control over the database. Thanks! Feb 3, 2023 at 19:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.