meta_query for a string inside a meta field containing a comma-separated list

The question:

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”?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

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.


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment