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

Question

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',
],