meta_query check for meta value in key which holds an array of values

Question

In every post of my custom post type called "announcements" I save the users' ID inside an array which resides in a meta_key called "post_is_read" whenever a user reads an announcement.

Just to explain: "post_is_read" contains an array (1, 2, 5, 12, 86, 100) where every number represents a user ID.

I achieved this using this code: https://wordpress.stackexchange.com/a/344315/15801

I am now trying to create a template which will show the posts a user has not read with the following code:

$current_user = wp_get_current_user();
$up_an_query_args = array(
    'post_type'      => 'announcement',
    'post_status'    => 'publish',
    'posts_per_page' => -1,
    'orderby'        => 'date', 
    'order'          => 'DSC',
    'meta_query' => array(
        array(
            'key'     => 'post_is_read', 
            'value'   => $current_user->ID,
            'compare' => 'NOT IN',  
        )
    ),                                      
);

I have read every related article/answer I've found both on and off wordpress stackexchange and no method worked for me. The most I could get it to work is using 'compare' => 'NOT LIKE' but it messed up the posts being displayed for users having at least a common digit in their IDs (ex between 1, 10, 11... etc) which is something I understand why is happening.

Answer 1

Revised Answer

^{( PS: I actually wanted to revise this answer long ago, but eventually I kept forgetting to do so. :) )}

So in this revised answer, the main point is the first one below, but I hope the rest also help you:

1. What you're trying to do is not going to be (easily) possible with the way the IDs are stored, which is serialized such as a:3:{i:0;i:1;i:1;i:10;i:2;i:11;} (for array( 1, 10, 11 )).

   What if the current user ID is 10 and in the meta value, there's an i:10;i:9, i.e. array item keyed 10 with the value (a user ID) of 9 (i.e. $some_variable[10] = 9), but the ID 10 is not actually in the list?

2. So because of the above, you might better off create a custom database table and store the user ID and post ID in their own column.

   And if you want, you can check a simplified example/demo on DB Fiddle.

3. Or you could instead store each user ID in individual post meta named post_is_read. That way, filtering the posts would be easy — but the post meta table would end up being really huge with many, many rows...

   // Saving the meta:
   add_post_meta( 1, 'post_is_read', 10 );
   add_post_meta( 1, 'post_is_read', 11 );
   
   // Then when filtering the posts:
   $user_id = 10;
   $args = array(
       'meta_key'     => 'post_is_read',
       'meta_value'   => $user_id,
       'meta_compare' => '!=',
   );
   $query = new WP_Query( $args );
   

4. Or you may use a serialized value, but store the user name/login and not the ID:

   // Store usernames and not IDs.
   $usernames = array( 'foo', 'user-bar', 'etc_etc' );
   
   update_post_meta( 123, 'post_is_read', $usernames );
   

   And then you can use the NOT LIKE comparison like so:

   $username = 'user-bar';
   $args = array(
       'meta_key'     => 'post_is_read',
       'meta_value'   => '"' . $username . '"', // search for exactly "<username>" (including the quotes)
       'meta_compare' => 'NOT LIKE',
   );
   $query = new WP_Query( $args );
   

Original Answer

Please check the revisions, but basically in my original answer, I was saying if you'd rather use the user IDs, then you could store them as a comma-separated list like 1,10,11 and then use the NOT REGEXP comparison:

// Saving the meta:
update_post_meta( 1, 'post_is_read', '1,10,11' );

// Then when filtering the posts:
$user_id = 10;
$args = array(
    'meta_key'     => 'post_is_read',
    'meta_value'   => "(^|,)$user_id(,|$)",
    'meta_compare' => 'NOT REGEXP',
);
$query = new WP_Query( $args );

And that did (and still does) work (with WordPress 5.6.1), except that it needs an extra parsing when retrieving the meta value, e.g.:

$ids = get_post_meta( 1, 'post_is_read', true );
$ids = wp_parse_id_list( $ids ); // convert to array

Answer 2

I store my IDs as strings so this is what I do

$up_an_query_args = array(
'post_type'      => 'announcement',
'post_status'    => 'publish',
'posts_per_page' => -1,
'orderby'        => 'date', 
'order'          => 'DSC',
'meta_query' => array(
    array(
        'key'     => 'post_is_read', 
        'value'   => serialize(strval($current_user->ID)),
        'compare' => 'LIKE',  
    )
),);

The same should work even if you store it as int:

'value'   => serialize($current_user->ID)