meta_query compare=’!=’ with multiple custom fields

2 comments

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.

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.

Comments are closed.