WordPress.org

Make WordPress Core

Opened 5 years ago

Closed 5 years ago

#29181 closed enhancement (fixed)

'EXISTS' and 'NOT EXISTS' operators for WP_Tax_Query

Reported by: boonebgorges Owned by: boonebgorges
Milestone: 4.1 Priority: normal
Severity: normal Version:
Component: Taxonomy Keywords:
Focuses: Cc:

Description

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

Attachments (1)

29181.patch (3.4 KB) - added by boonebgorges 5 years ago.

Download all attachments as: .zip

Change History (6)

@boonebgorges
5 years ago

#1 @wonderboymusic
5 years ago

  • Milestone changed from Awaiting Review to Future Release

4.1

#2 @boonebgorges
5 years ago

  • Milestone changed from Future Release to 4.1
  • Owner set to boonebgorges
  • Status changed from new to assigned

#3 @DrewAPicture
5 years ago

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

This ticket was mentioned in IRC in #wordpress-dev by boonebgorges. View the logs.


5 years ago

#5 @boonebgorges
5 years ago

  • Resolution set to fixed
  • Status changed from assigned to closed

[29896] missed the ticket.

Note: See TracTickets for help on using tickets.