How to check current user in meta value array in WP_Query meta_query

Question

I am having trouble to query cpt for the current user if s/he is in the meta value array.

query args

$args = [
    'post_type'      => $cp::get_module_cpt(),
    'posts_per_page' => - 1,
    'meta_query'     => [
        'relation' => 'AND',
        [
            'key'     => 'premium_module',
            'value'   => 0,
            'compare' => '=',
        ],
        [
            'key'     => 'permit_users',
            'value'   => get_current_user_id(),
            'compare' => 'IN',
        ],
    ],
];

meta key

permit_users

meta value

if has value

Array
(
    [0] => 29
    [1] => 28
    ...
)

if has no value

return false or empty array. Depends if ever set the field before.

additionally,

I have tried setting compare to = equal but that also didn't work. Probably since the value is in array.

Result Looking

The query should return all posts that have current user id in the permit_users key.

Important:
The permit_users is an optional, so if the field has not value (false) or empty array, the query should ignore it.

Answer 1

You can't easily query inside arrays inside a meta value. This is because there's no such thing as an array value in MySQL. Instead the value is stored as a serialized string.

So an array like this:

array(
    0 => 29,
    1 => 28,
);

Is stored in the database as this string:

a:2:{i:0;i:29;i:1;i:28;}

So the only way to find values inside it is to do a LIKE on the string. But the problem is that if you want to check if the value 28 is in the array, you'd need to query LIKE '%:28;%', but apart from being much slower, could give you incorrect results if 28 is an index in the array.

So instead of storing an array, use add_post_meta() to add multiple rows for the same meta key. For example, if you use:

update_post_meta( $post_id, 'permit_users', [ 29, 28 ] );

You will get:

+--------------+--------------------------+
|   meta_key   |        meta_value        |
+--------------+--------------------------+
| permit_users | a:2:{i:0;i:29;i:1;i:28;} |
+--------------+--------------------------+

But if you use:

add_post_meta( $post_id, 'permit_user', 28 );
add_post_meta( $post_id, 'permit_user', 29 );

You will get:

+-------------+------------+
|  meta_key   | meta_value |
+-------------+------------+
| permit_user |         28 |
| permit_user |         29 |
+-------------+------------+

And your WP_Query using IN will work the way you expect:

[
    'key'     => 'permit_user',
    'value'   => get_current_user_id(),
    'compare' => 'IN',
],