WordPress meta query check the current users in meta value array

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

Database

a:5:{i:0;s:2:"23";i:1;s:1:"2";i:2;s:1:"6";i:3;s:1:"7";i:4;s:2:"27";}

In PHP

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.

Update

I have tried compare with LIKE that seems working. But is it good to compare serialized data? What if current user is 11 and value has 111 user in it?

Answer 1

My suggestion is if this Custom Post Type is developed by you means try to unserialize and save each user in individual row something like this

meta_key in the format permit_users_{user_id} with the value user_id.

Save values in the above format with meta unique is set to true (the last parameter)

add_post_meta( 68, 'permit_users_' . get_current_user_id(), '21', true );

Else your project DB is now mature very much and difficult to do the above suggestion change then you can use the below meta query

$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'   => sprintf(':"%s";', get_current_user_id()),
            'compare' => 'LIKE',
        ],
    ],
];

The above one will work with serialized data with the value 11, 111 but please consider this as a temporary solution.

Again I'm requesting you to try to implement the conversion of serialized data into an individual own row of values.

Additional note: if serialized string is very lengthy and in worst case matching querying user id from the lengthy string will take time. So maintain this serialized data in shorter length but don't get worry and do early optimization wait till slow query log record this then we can go for optimized solution like adding indexing or adding a new search engine layer for e.g. Elastic search.