Negative meta_query if storing multiple post_meta values with shared meta_key

Question

WordPress supports storing multiple post meta values for a single post using the same meta key. For example one post can have multiple instances of meta_key 'pet' with meta_value 'cat', 'dog', and/or 'fish'. This is super useful when using get_post_meta because you can get an array of all values for the pet meta key, versus managing a serialized array in a single field.

It's also really useful when using a meta query, because you can query any post where meta_key 'pet' = 'cat', and get posts where any instance of 'pet' is equal to 'cat', regardless of how many other instances of the meta key may be associated with the post with different values.

But is the opposite (e.g. != or NOT LIKE or NOT IN) possible? I'm using the following and it's returning lots of posts that have one instance of meta_key 'pet' = 'cat'.

$pet_owners = new WP_Query([
  'post_type' => 'pet_owner',
  'posts_per_page' => -1,
  'meta_query' => [
    [
      'key' => 'pet',
      'value' => 'cat',
      'compare' => '!='
    ]
  ]
]);

Edit: I had an error in my pseudocode as flagged by Buttered_toast below.

Answer 1

Thats because you created the meta_query property incorrectly, it should be a multi diminsional array.

$pet_owners = new WP_Query([
    'post_type' => 'pet_owner',
    'posts_per_page' => -1,
    'meta_query' => [
        [
            'key' => 'pet',
            'value' => 'cat',
            'compare' => '!='
        ]
    ]
]);

For more information about meta_query see Custom Field (post meta) Parameters

Answer 2

In digging deeper into my issue it seemed like the only way to do this would be to do two queries:

First, query all pet owners who have a cat

$cat_owners = new WP_Query([
  "post_type" => "pet_owners", 
  "posts_per_page" => -1, 
  "meta_key" => "animal", 
  "meta_value" => "cat"
])

Then, query all pet owners that are not in the results of the first query, but own at least one pet (hence the "meta_compare" => "EXISTS"):

$not_cat_owners = new WP_Query([
  "post_type" => "pet_owners", 
  "posts_per_page" => -1, 
  "post__not_in" => $cat_owners,
  "meta_key" => "animal", 
  "meta_compare" => "EXISTS"
])

This would result in a list of pet owners that don't have a cat, but it feels silly to do two WP_Queries for one set of results, so instead I opted to query directly against the DB using the $wpdb class.

This allows for the following:

$wpdb->get_results("SELECT ID FROM $wpdb->posts 
  WHERE post_status = 'publish' 
  AND post_type = 'pet_owner' 
  AND ID IN
    (SELECT post_id FROM $wpdb->postmeta 
     WHERE meta_key = 'animal')
  AND ID NOT IN 
    (SELECT post_id FROM $wpdb->postmeta 
     WHERE meta_key = 'animal' 
     AND meta_value = 'cat')");

Which I guess technically is three queries (the main query and the two nested queries), but seems cleaner and simpler. I'd be interested to know if someone with better SQL skills than me can come up with a more performant approach to this.