Filter posts with meta_query NOT IN where value has multiple values

Question

I would like to use a meta_key to store multiple values in that key. Thereafter, I would like to filter away posts that do not contain a specific value in the key. In other words, just show posts that do not contain a specific value. This, however, does not seem to be possible. I am writing this question here to see if anyone has come to the same conclusion or if I am just missing something. Here is the code I have used:

// The values in key "_no_interest" are: 1,2,3
add_action( 'pre_get_posts', 'custom_pre_query' );
function custom_pre_query( $query ) {
    if( $query->is_main_query() && !is_admin() && $query->is_post_type_archive( 'my_custom_post_type' ) ) {
        $meta_query = array(
            array(
                'key'       => '_no_interest',
                'value'     => '3',
                'compare'   => 'NOT IN'
            )
        );
        $query->set( 'meta_query', $meta_query );
        $query->set( 'posts_per_page', -1 );
        $query->set( 'post_status', 'publish' );
        $query->set( 'orderby', 'date' );   
    }
}

This will not work and neither will it if I use the '!=' operator. However, if I set compare to be equal to 'IN' or '=', then it will work.

Can anybody confirm this or am I missing something here?

Answer 1

The NOT IN and != comparison operators work fine, however be aware that if you are using meta then your data may not be working in the way you expect. Specifically, meta_keys don't have to be unique per post. A post can have _no_interest set to 1 AND have another meta row with it set to 2 as well.

So for example, if you have a post with _no_interest set to 1 and also set to 2 in another row, then even though you exclude 2, this post will still appear because it has a 1 on it in a different row.

Also, if you use IN or NOT IN, then your "value" parameter really should be an array of the values. So your value of '3' there should be array('3') instead.

Also be aware that having a meta that doesn't exist for a post will give you unexpected results with a NOT IN as well. If you are doing posts with meta NOT IN some value, then posts which don't have that meta at all also will not show up. In other words, they will have to have A value for the meta, just not THAT particular value. This is because specifying a meta causes an INNER JOIN to occur, which eliminates rows that lack that meta. The exception to this is using the NOT EXISTS comparison.