meta_query compare='!=' with multiple custom fields

Question

a post has the following custom fields:

'my_custom_field' => 1
'my_custom_field' => 2
'my_custom_field' => 3

I now want to query all posts that do NOT contain the value 2 for 'my_custom_field'. I'm trying this:

$args = Array('posts_per_page' => -1,
    'post_type' => 'page',
    'meta_query' => array(
      array(
        'key' => 'my_custom_field',
        'value' => 2,
        'compare' => '!='
      )
    )
  );

However, this is still returning my sample post, as my sample post has a field of 'my_custom_field' with a value other than 2 (1 and 3). I somehow need to change my query to say "Exclude posts that have at least one field of 'my_custom_field' with the value of 2".

Can anyone help me? Thanks!

Answer 1

This might require writing custom sql with a subquery:

$results = $wpdb->get_results("
    SELECT * 
    FROM $wpdb->posts 
    WHERE ID NOT IN (
      SELECT post_id FROM $wpdb->postmeta
      WHERE meta_key = 'my_custom_field' and meta_value = 2
    )
");

then just iterate through the results as described here.

Answer 2

As @TomJNowell and @s_ha_dum says you in comments, in your case probably a custom taxonomy is better than custom fields, because is very easy to get a post that has not a taxonomy term.

That said, I understand that if you have a lot of code alreadt wrote, or you are working with third party code, moving to custom taxonomy can be hard / not possible.

Aswer provided by @elleeott works, however is not flexible, because you can't change posts per page, meta value, etc..

I'll provide you a similar solution, but a bit more flexible and you can fine tune it to fit your needs.

Essentially is just a class that extend WP_Query and apply filters to request to perform a what you want

class MyMetaQuery extends WP_Query {

   protected $do_posts_request_filter = TRUE;

   function get_posts() {
     $this->query_vars['suppress_filters'] = FALSE; // force do filters
     add_action( 'posts_request', array( $this, 'filter_request' ), 999 );
     parent::get_posts();
     $this->do_posts_request_filter = FALSE; // run only once
   }

   /* build the sql request */
   function filter_request( $request ) {
      if ( ! $this->do_posts_request_filter ) return $request;
      $q = $this->get('meta_query');
      if ( empty($q) || ! is_array($q) || ! isset($q[0]['key']) ) return $request;
      global $wpdb;
      $k = $q[0]['key'];
      $v = isset( $q[0]['value'] ) ? $q[0]['value'] : '';
      if ( is_array( $v ) ) $v = implode( ',', $v );
      $mcompare = isset( $q[0]['compare'] ) ? $q[0]['compare'] : 'IN';
      if ( ! in_array( $mcompare, array('IN', 'NOT IN', '=', '!=') ) ) {
        $mcompare = 'IN';
      }
      if ( $mcompare === '!=' ) $mcompare = 'NOT IN';
      if ( $mcompare === '=' ) $mcompare = 'IN';
      $ppage = $this->get('posts_per_page') ? : get_option('posts_per_page');
      $limit = $wpdb->prepare(' LIMIT %d', $ppage);
      if ( substr_count( $limit, '-1' )  ) $limit = '';
      if ( $limit && $this->get('nopaging') ) $limit = '';
      $type_compare = 'IN';
      $type = $this->get('post_type') ? : 'post';
      if ( is_array( $type ) ) $type = implode(',', $type );
      if ( $type === 'any' ) {
        $type_compare = 'NOT IN';
        $status = '-1';
      }
      $status_compare = 'IN';
      $status = $this->get('post_status') ? : 'publish';
      if ( is_array($status) ) $type = implode(',', $status);
      if ( $status === 'any' ) {
        $status_compare = 'NOT IN';
        $status = '-1';
      }
      $query = "
        SELECT SQL_CALC_FOUND_ROWS $wpdb->posts.* FROM $wpdb->posts
        INNER JOIN $wpdb->postmeta ON $wpdb->posts.ID = $wpdb->postmeta.post_id
        WHERE post_type $type_compare (%s)
        AND post_status $status_compare (%s)
        AND $wpdb->postmeta.meta_key = %s
        AND ID NOT IN (
          SELECT DISTINCT post_id FROM $wpdb->postmeta
          WHERE meta_key = %s and CAST(meta_value AS CHAR) $mcompare (%s)
        )
      ";
      return $wpdb->prepare( $query, $type, $status, $k, $k, $v ) . $limit;
   }

}

After that, instead of using WP_Query you have to use MyMetaQuery class:

$args = array(
  'posts_per_page' => -1,
  'post_type' => 'page',
  'meta_query' => array(
    array( 'key' => 'my_custom_field', 'value' => 2, 'compare' => '!=' )
  )
);

$query = new MyMetaQuery( $args );

if ( $query->have_posts() ) {
  while( $query->have_posts() ) {
    $query->the_post(); 
    // do what you want here
  }
  wp_reset_postdata();
}

Note thats the only params the custom class accepts are:

• 'post_type', can be a single types, a comma-separed string or an array of types or 'any'
• 'post_status' can be a single status, a comma-separed string or an array of statuses or 'any'
• 'posts_per_page' can be a posistive number or '-1'
• 'nopaging' can be TRUE or FALSE. Set to TRUE will return all posts overriding 'posts_per_page'
• 'meta_query' accepts a multidimensional array, just like WP_Query. Note that you can only pass one query array, any additional query will be ignored. The query array accepts as array keys: 'key', 'value' and 'compare'. 'value' can be a single value, a comma-separed string or an array of values. 'compare' can be 'IN', 'NOT IN', '=' and '!='

If you need other arguments (category, tags, taxonomies, date, etc) you must extend the class features or, more probably, use another approach.