[wp-trac] [WordPress Trac] #29181: 'EXISTS' and 'NOT EXISTS' operators for WP_Tax_Query

[WordPress Trac]

#29181: 'EXISTS' and 'NOT EXISTS' operators for WP_Tax_Query
--------------------------+-----------------------------
 Reporter:  boonebgorges  |      Owner:
     Type:  enhancement   |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Taxonomy      |    Version:
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 It is sometimes desirable to query for posts that:
 a. contain *any* term from a given taxonomy, or
 b. contain *no* terms from a given taxonomy

 Currently, the only way to do this is by running a separate query to get
 all terms belonging to the taxonomy in question:

 {{{
 $terms_in_taxonomy = get_terms( 'foo', array(
     'hide_empty' => false,
     'fields' => 'ids',
 ) );

 $posts_query = new WP_Query( array(
     'tax_query' => array(
         array(
             'taxonomy' => 'foo',
             'terms' => $terms_in_taxonomy,
             'operator' => 'NOT IN', // or 'IN'
         ),
     ),
 ) );
 }}}

 Aside from being inelegant, this is also less than ideal from a
 performance point of view, because the list of terms can in some cases be
 quite large; storing that list of IDs in PHP and passing it through to
 WP_Query is slower than letting MySQL handle this stuff.

 ==

 My suggested solution is to introduce 'EXISTS' and 'NOT EXISTS' operators
 for WP_Tax_Query. See attached patch.

 A few implementation notes:
 - Unit tests included
 - Using 'EXISTS' or 'NOT EXISTS' causes the 'terms' to be ignored. An
 'EXISTS' query with terms would be the same as using 'IN', so I thought it
 was redundant.
 - It's possible to rewrite the SQL to use 'IN' instead of 'EXISTS' (or
 'NOT IN' instead of 'NOT EXISTS'). I tested the two variants on a fairly
 large installation (wp_posts ~ 250K records, wp_term_taxonomy ~ 70K
 records; wp_term_relationships ~500K records). In those tests, EXISTS/NOT
 EXISTS performed 5-10% faster. Here's a brief post with a bit of
 background on the difference in the internals:
 http://stackoverflow.com/questions/14190788/subqueries-with-exists-vs-in-
 mysql

--
Ticket URL: <https://core.trac.wordpress.org/ticket/29181>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform