0

In my project the WP users have a custom meta field "usergroup" containing a comma separated list of values. The meta field can contain values like this:

WS,CT,IS,TS,TS-IS,TS-WS,TS-CT

Now I would like to create a meta_query() that returns all users that have for example the value "IS" in this field. I can achieve this with this code:

    $args = array(
        'meta_query' => array(
            array(
                'key' => 'usergroup',
                'value' => 'IS'
                'compare'   => 'IN'
            )
        )
    );
    $users = get_users($args);

But compare => IN matches for all occurences of "IS", so also for "TS-IS", which is not desired.

Is there a smart way to meta_query so it matches just "IS, but not "TS-IS"?

7
  • This is actually more of a generic SQL question, but yes, you would want to use REGEXP as the compare value and then set the value to something like (^|,)IS(,|$). And be sure to properly escape the IS or whatever is the actual value.
    – Sally CJ
    Jun 12 at 2:49
  • Interesting, will it work with with comma before value 'value' => ',IS' ?
    – anton
    Jun 12 at 10:32
  • 1
    Yes. Sally's regular expression accounts for IS bounded on the left by either the start of the string or a comma and bounded on the right by either a comma or the end of the string. There are a number of sites available to experiment with and help interpret regular expressions - I like regex101.com
    – bosco
    Jun 12 at 15:52
  • 1
    @Nicolai, that looks good to me. So are you sure in the database, the meta value is indeed a comma-separated list like CT,IS? Try SELECT meta_value FROM wp_usermeta WHERE meta_key = 'usergroup' LIMIT 5 on phpMyAdmin or a similar tool, and just check the meta values. (Change the table prefix if it's not wp_) And how did you add the meta field; using a plugin?
    – Sally CJ
    Jun 13 at 23:30
  • 1
    @SallyCJ: Sorry for the late reply. Your guess was right, a check in the databse revealed that the values are actually stored serialized, like this: a:11:{i:0;s:2:"IS";i:1;s:5:"TS-IS";i:2;s:6:"TS-USA";i:3;s:2:"TS";i:4;s:3:"EKG";i:5;s:2:"WS";i:6;s:2:"CT";i:7;s:6:"TS-MAX";i:8;s:5:"TS-XD";i:9;s:6:"TS-CTM";i:10;s:5:"TS-MT";} The meta field is added using the Advanced Custom Fields plugin. I guess REGEXP is not useful for my case then. Still the original question and your solution to it is very useful in my opinion, just not for my specific case it seems.
    – Nicolai
    Jun 25 at 10:04
2

Is there a smart way to meta_query so it matches just "IS, but not "TS-IS"?

The WordPress meta query class (WP_Meta_Query), which among others, is used with the posts (WP_Query) and users (WP_User_Query) query classes, supports REGEXP (since WordPress 3.7) as the compare value, so you could use that with a RegEx (regular expresion) pattern like (^|,)IS(,|$), like so:

'meta_query' => array(
    array(
        'key'     => 'usergroup',
        'compare' => 'REGEXP',
        // With dynamic values, be sure to properly escape the "IS" or whatever
        // is the actual value. E.g. '(^|,)' . preg_quote( $value ) . '(,|$)'
        'value'   => '(^|,)IS(,|$)',
    ),
),

So that should work well for meta values that in the database are stored in the form of a comma-separated list, i.e. value, value, value, ... (with or without the whitespaces), and you can test the above pattern here.

However, since you said (in the comments) that the meta values are actually serialized like so:

a:11:{i:0;s:2:"IS";i:1;s:5:"TS-IS";i:2;s:6:"TS-USA";i:3;s:2:"TS";i:4;s:3:"EKG";i:5;s:2:"WS";i:6;s:2:"CT";i:7;s:6:"TS-MAX";i:8;s:5:"TS-XD";i:9;s:6:"TS-CTM";i:10;s:5:"TS-MT";}

Then you would instead want to use LIKE as the compare value, but use "<value>" as the value's value like this:

'meta_query' => array(
    array(
        'key'     => 'usergroup',
        'compare' => 'LIKE',
        // With dynamic values, be sure to properly escape the "IS" or whatever
        // is the actual value.
        'value'   => '"IS"',
    ),
),

Try a demo on DB Fiddle.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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