meta_query with relation = AND not working as expected when combining EQUALS with NOT EQUALS

Question

I want to query my player post type for all those who do not have certain colortoken meta values (note that a player can have multiple colortoken meta_keys). I assumed I could do this:

$args = array(
    'post_type'         => 'player',
    'post_status'       => 'any',
    'posts_per_page'    => 1,

    'meta_query'        => array(
        'relation' => 'AND',
        array(
            'key'     => 'playerlevel',
            'value'   => 12,
        ),
        array(
            'key'       => 'colortoken',
            'value'     => 'blue',
            'compare' => '!=',
        ),
    ),
);

$playersQuery = get_posts($args);

This should return all players who are level 12 but don't have a blue colortoken. This works... but only if a player has a single colortoken. If I have a player post with two colortoken meta_values:

Post ID     => 158
playerlevel => 12
colortoken  => red
colortoken  => blue

...then this player (ID 158) will be returned within the array, despite the fact that I asked for colortoken != blue. This seems to be an issue when there are multiple meta values with the same key, but the one you're trying to negate isn't the first one (so, if blue was the first value rather than the second, or it was the only colortoken meta value, then post 158 wouldn't be returned).

This only seems to be a problem when I combine relation => AND with a compare => '=' & compare => '!='. If both of the compares are = (or removed, since it's implicit), then the returned array is as expected, even with multiple duplicate meta key/value pairs.

Am I missing something, or is this some kind of expected behaviour?

Answer 1

That player does have a colortoken which is not blue, because he has a red one. :)

SQL, and by extension the WP_Query system, is extremely literal. It's selecting a set of players who have a colortoken that is not blue and a level of 12. It's not excluding players with blue tokens, it's just selecting all of those that are not blue.

What you want here cannot be done in a WP_Query. While it is a good abstraction on top of SQL, it is not a perfect one and it cannot represent all possible queries that can be made. In particular, WP_Query is designed to select posts to include based on parameters. Here, you're wanting to exclude a set of posts based on that blue colortoken, and the meta_query doesn't have a way to do this.

Here's what your query looks like as you gave it, after WP_Query parses it (somewhat simplified):

SELECT wp_posts.ID FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
INNER JOIN wp_postmeta AS mt1 ON ( wp_posts.ID = mt1.post_id ) 
WHERE 1=1 AND 
( 
( wp_postmeta.meta_key = 'playerlevel' AND wp_postmeta.meta_value = '12' ) AND 
( mt1.meta_key = 'colortoken' AND mt1.meta_value != 'blue' ) 
) AND 
wp_posts.post_type = 'player' AND 
((wp_posts.post_status <> 'trash' AND wp_posts.post_status <> 'auto-draft')) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 1

To actively exclude blue tokens, you need to use a subquery to exclude them. WP_Query can't do subqueries very well. Here's what will get you the posts you want to get:

SELECT wp_posts.ID FROM wp_posts 
INNER JOIN wp_postmeta ON ( wp_posts.ID = wp_postmeta.post_id ) 
WHERE 1=1 AND 
wp_posts.ID NOT IN ( 
SELECT post_id FROM wp_postmeta WHERE 
meta_key = 'colortoken' AND meta_value = 'blue' 
) AND
( wp_postmeta.meta_key = 'playerlevel' AND wp_postmeta.meta_value = '12' ) AND 
wp_posts.post_type = 'player' AND 
((wp_posts.post_status <> 'trash' AND wp_posts.post_status <> 'auto-draft')) 
GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_date DESC LIMIT 0, 1

That basically selects all level 12 players, except those with a blue token.