'EXISTS' and 'NOT EXISTS' operators for WP_Tax_Query

[boonebgorges]

It is sometimes desirable to query for posts that:

1. contain *any* term from a given taxonomy, or
2. 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

[wonderboymusic]

4.1

[DrewAPicture]

Just leaving a note that any changes here will need corresponding docs updates.

[boonebgorges]

[29896] missed the ticket.