3 * Taxonomy API: WP_Tax_Query class
11 * Core class used to implement taxonomy queries for the Taxonomy API.
13 * Used for generating SQL clauses that filter a primary query according to object
16 * WP_Tax_Query is a helper that allows primary query classes, such as WP_Query, to filter
17 * their results by object metadata, by generating `JOIN` and `WHERE` subclauses to be
18 * attached to the primary SQL query string.
25 * Array of taxonomy queries.
27 * See {@see WP_Tax_Query::__construct()} for information on tax query arguments.
33 public $queries = array();
36 * The relation between the queries. Can be one of 'AND' or 'OR'.
45 * Standard response when the query should not return any rows.
53 private static $no_results = array( 'join' => array( '' ), 'where' => array( '0 = 1' ) );
56 * A flat list of table aliases used in the JOIN clauses.
62 protected $table_aliases = array();
65 * Terms and taxonomies fetched by this query.
67 * We store this data in a flat array because they are referenced in a
68 * number of places by WP_Query.
74 public $queried_terms = array();
77 * Database table that where the metadata's objects are stored (eg $wpdb->users).
83 public $primary_table;
86 * Column in 'primary_table' that represents the ID of the object.
92 public $primary_id_column;
98 * @since 4.1.0 Added support for `$operator` 'NOT EXISTS' and 'EXISTS' values.
101 * @param array $tax_query {
102 * Array of taxonomy query clauses.
104 * @type string $relation Optional. The MySQL keyword used to join
105 * the clauses of the query. Accepts 'AND', or 'OR'. Default 'AND'.
107 * Optional. An array of first-order clause parameters, or another fully-formed tax query.
109 * @type string $taxonomy Taxonomy being queried. Optional when field=term_taxonomy_id.
110 * @type string|int|array $terms Term or terms to filter by.
111 * @type string $field Field to match $terms against. Accepts 'term_id', 'slug',
112 * 'name', or 'term_taxonomy_id'. Default: 'term_id'.
113 * @type string $operator MySQL operator to be used with $terms in the WHERE clause.
114 * Accepts 'AND', 'IN', 'NOT IN', 'EXISTS', 'NOT EXISTS'.
116 * @type bool $include_children Optional. Whether to include child terms.
117 * Requires a $taxonomy. Default: true.
121 public function __construct( $tax_query ) {
122 if ( isset( $tax_query['relation'] ) ) {
123 $this->relation = $this->sanitize_relation( $tax_query['relation'] );
125 $this->relation = 'AND';
128 $this->queries = $this->sanitize_query( $tax_query );
132 * Ensure the 'tax_query' argument passed to the class constructor is well-formed.
134 * Ensures that each query-level clause has a 'relation' key, and that
135 * each first-order clause contains all the necessary keys from `$defaults`.
140 * @param array $queries Array of queries clauses.
141 * @return array Sanitized array of query clauses.
143 public function sanitize_query( $queries ) {
144 $cleaned_query = array();
149 'field' => 'term_id',
151 'include_children' => true,
154 foreach ( $queries as $key => $query ) {
155 if ( 'relation' === $key ) {
156 $cleaned_query['relation'] = $this->sanitize_relation( $query );
158 // First-order clause.
159 } elseif ( self::is_first_order_clause( $query ) ) {
161 $cleaned_clause = array_merge( $defaults, $query );
162 $cleaned_clause['terms'] = (array) $cleaned_clause['terms'];
163 $cleaned_query[] = $cleaned_clause;
166 * Keep a copy of the clause in the flate
167 * $queried_terms array, for use in WP_Query.
169 if ( ! empty( $cleaned_clause['taxonomy'] ) && 'NOT IN' !== $cleaned_clause['operator'] ) {
170 $taxonomy = $cleaned_clause['taxonomy'];
171 if ( ! isset( $this->queried_terms[ $taxonomy ] ) ) {
172 $this->queried_terms[ $taxonomy ] = array();
176 * Backward compatibility: Only store the first
177 * 'terms' and 'field' found for a given taxonomy.
179 if ( ! empty( $cleaned_clause['terms'] ) && ! isset( $this->queried_terms[ $taxonomy ]['terms'] ) ) {
180 $this->queried_terms[ $taxonomy ]['terms'] = $cleaned_clause['terms'];
183 if ( ! empty( $cleaned_clause['field'] ) && ! isset( $this->queried_terms[ $taxonomy ]['field'] ) ) {
184 $this->queried_terms[ $taxonomy ]['field'] = $cleaned_clause['field'];
188 // Otherwise, it's a nested query, so we recurse.
189 } elseif ( is_array( $query ) ) {
190 $cleaned_subquery = $this->sanitize_query( $query );
192 if ( ! empty( $cleaned_subquery ) ) {
193 // All queries with children must have a relation.
194 if ( ! isset( $cleaned_subquery['relation'] ) ) {
195 $cleaned_subquery['relation'] = 'AND';
198 $cleaned_query[] = $cleaned_subquery;
203 return $cleaned_query;
207 * Sanitize a 'relation' operator.
212 * @param string $relation Raw relation key from the query argument.
213 * @return string Sanitized relation ('AND' or 'OR').
215 public function sanitize_relation( $relation ) {
216 if ( 'OR' === strtoupper( $relation ) ) {
224 * Determine whether a clause is first-order.
226 * A "first-order" clause is one that contains any of the first-order
227 * clause keys ('terms', 'taxonomy', 'include_children', 'field',
228 * 'operator'). An empty clause also counts as a first-order clause,
229 * for backward compatibility. Any clause that doesn't meet this is
230 * determined, by process of elimination, to be a higher-order query.
237 * @param array $query Tax query arguments.
238 * @return bool Whether the query clause is a first-order clause.
240 protected static function is_first_order_clause( $query ) {
241 return is_array( $query ) && ( empty( $query ) || array_key_exists( 'terms', $query ) || array_key_exists( 'taxonomy', $query ) || array_key_exists( 'include_children', $query ) || array_key_exists( 'field', $query ) || array_key_exists( 'operator', $query ) );
245 * Generates SQL clauses to be appended to a main query.
252 * @param string $primary_table Database table where the object being filtered is stored (eg wp_users).
253 * @param string $primary_id_column ID column for the filtered object in $primary_table.
255 * Array containing JOIN and WHERE SQL clauses to append to the main query.
257 * @type string $join SQL fragment to append to the main JOIN clause.
258 * @type string $where SQL fragment to append to the main WHERE clause.
261 public function get_sql( $primary_table, $primary_id_column ) {
262 $this->primary_table = $primary_table;
263 $this->primary_id_column = $primary_id_column;
265 return $this->get_sql_clauses();
269 * Generate SQL clauses to be appended to a main query.
271 * Called by the public WP_Tax_Query::get_sql(), this method
272 * is abstracted out to maintain parity with the other Query classes.
278 * Array containing JOIN and WHERE SQL clauses to append to the main query.
280 * @type string $join SQL fragment to append to the main JOIN clause.
281 * @type string $where SQL fragment to append to the main WHERE clause.
284 protected function get_sql_clauses() {
286 * $queries are passed by reference to get_sql_for_query() for recursion.
287 * To keep $this->queries unaltered, pass a copy.
289 $queries = $this->queries;
290 $sql = $this->get_sql_for_query( $queries );
292 if ( ! empty( $sql['where'] ) ) {
293 $sql['where'] = ' AND ' . $sql['where'];
300 * Generate SQL clauses for a single query array.
302 * If nested subqueries are found, this method recurses the tree to
303 * produce the properly nested SQL.
308 * @param array $query Query to parse, passed by reference.
309 * @param int $depth Optional. Number of tree levels deep we currently are.
310 * Used to calculate indentation. Default 0.
312 * Array containing JOIN and WHERE SQL clauses to append to a single query array.
314 * @type string $join SQL fragment to append to the main JOIN clause.
315 * @type string $where SQL fragment to append to the main WHERE clause.
318 protected function get_sql_for_query( &$query, $depth = 0 ) {
330 for ( $i = 0; $i < $depth; $i++ ) {
334 foreach ( $query as $key => &$clause ) {
335 if ( 'relation' === $key ) {
336 $relation = $query['relation'];
337 } elseif ( is_array( $clause ) ) {
339 // This is a first-order clause.
340 if ( $this->is_first_order_clause( $clause ) ) {
341 $clause_sql = $this->get_sql_for_clause( $clause, $query );
343 $where_count = count( $clause_sql['where'] );
344 if ( ! $where_count ) {
345 $sql_chunks['where'][] = '';
346 } elseif ( 1 === $where_count ) {
347 $sql_chunks['where'][] = $clause_sql['where'][0];
349 $sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )';
352 $sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] );
353 // This is a subquery, so we recurse.
355 $clause_sql = $this->get_sql_for_query( $clause, $depth + 1 );
357 $sql_chunks['where'][] = $clause_sql['where'];
358 $sql_chunks['join'][] = $clause_sql['join'];
363 // Filter to remove empties.
364 $sql_chunks['join'] = array_filter( $sql_chunks['join'] );
365 $sql_chunks['where'] = array_filter( $sql_chunks['where'] );
367 if ( empty( $relation ) ) {
371 // Filter duplicate JOIN clauses and combine into a single string.
372 if ( ! empty( $sql_chunks['join'] ) ) {
373 $sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) );
376 // Generate a single WHERE clause with proper brackets and indentation.
377 if ( ! empty( $sql_chunks['where'] ) ) {
378 $sql['where'] = '( ' . "\n " . $indent . implode( ' ' . "\n " . $indent . $relation . ' ' . "\n " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')';
385 * Generate SQL JOIN and WHERE clauses for a "first-order" query clause.
390 * @global wpdb $wpdb The WordPress database abstraction object.
392 * @param array $clause Query clause, passed by reference.
393 * @param array $parent_query Parent query array.
395 * Array containing JOIN and WHERE SQL clauses to append to a first-order query.
397 * @type string $join SQL fragment to append to the main JOIN clause.
398 * @type string $where SQL fragment to append to the main WHERE clause.
401 public function get_sql_for_clause( &$clause, $parent_query ) {
411 $this->clean_query( $clause );
413 if ( is_wp_error( $clause ) ) {
414 return self::$no_results;
417 $terms = $clause['terms'];
418 $operator = strtoupper( $clause['operator'] );
420 if ( 'IN' == $operator ) {
422 if ( empty( $terms ) ) {
423 return self::$no_results;
426 $terms = implode( ',', $terms );
429 * Before creating another table join, see if this clause has a
430 * sibling with an existing join that can be shared.
432 $alias = $this->find_compatible_table_alias( $clause, $parent_query );
433 if ( false === $alias ) {
434 $i = count( $this->table_aliases );
435 $alias = $i ? 'tt' . $i : $wpdb->term_relationships;
437 // Store the alias as part of a flat array to build future iterators.
438 $this->table_aliases[] = $alias;
440 // Store the alias with this clause, so later siblings can use it.
441 $clause['alias'] = $alias;
443 $join .= " INNER JOIN $wpdb->term_relationships";
444 $join .= $i ? " AS $alias" : '';
445 $join .= " ON ($this->primary_table.$this->primary_id_column = $alias.object_id)";
449 $where = "$alias.term_taxonomy_id $operator ($terms)";
451 } elseif ( 'NOT IN' == $operator ) {
453 if ( empty( $terms ) ) {
457 $terms = implode( ',', $terms );
459 $where = "$this->primary_table.$this->primary_id_column NOT IN (
461 FROM $wpdb->term_relationships
462 WHERE term_taxonomy_id IN ($terms)
465 } elseif ( 'AND' == $operator ) {
467 if ( empty( $terms ) ) {
471 $num_terms = count( $terms );
473 $terms = implode( ',', $terms );
477 FROM $wpdb->term_relationships
478 WHERE term_taxonomy_id IN ($terms)
479 AND object_id = $this->primary_table.$this->primary_id_column
482 } elseif ( 'NOT EXISTS' === $operator || 'EXISTS' === $operator ) {
484 $where = $wpdb->prepare( "$operator (
486 FROM $wpdb->term_relationships
487 INNER JOIN $wpdb->term_taxonomy
488 ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
489 WHERE $wpdb->term_taxonomy.taxonomy = %s
490 AND $wpdb->term_relationships.object_id = $this->primary_table.$this->primary_id_column
491 )", $clause['taxonomy'] );
495 $sql['join'][] = $join;
496 $sql['where'][] = $where;
501 * Identify an existing table alias that is compatible with the current query clause.
503 * We avoid unnecessary table joins by allowing each clause to look for
504 * an existing table alias that is compatible with the query that it
507 * An existing alias is compatible if (a) it is a sibling of `$clause`
508 * (ie, it's under the scope of the same relation), and (b) the combination
509 * of operator and relation between the clauses allows for a shared table
510 * join. In the case of WP_Tax_Query, this only applies to 'IN'
511 * clauses that are connected by the relation 'OR'.
516 * @param array $clause Query clause.
517 * @param array $parent_query Parent query of $clause.
518 * @return string|false Table alias if found, otherwise false.
520 protected function find_compatible_table_alias( $clause, $parent_query ) {
523 // Sanity check. Only IN queries use the JOIN syntax .
524 if ( ! isset( $clause['operator'] ) || 'IN' !== $clause['operator'] ) {
528 // Since we're only checking IN queries, we're only concerned with OR relations.
529 if ( ! isset( $parent_query['relation'] ) || 'OR' !== $parent_query['relation'] ) {
533 $compatible_operators = array( 'IN' );
535 foreach ( $parent_query as $sibling ) {
536 if ( ! is_array( $sibling ) || ! $this->is_first_order_clause( $sibling ) ) {
540 if ( empty( $sibling['alias'] ) || empty( $sibling['operator'] ) ) {
544 // The sibling must both have compatible operator to share its alias.
545 if ( in_array( strtoupper( $sibling['operator'] ), $compatible_operators ) ) {
546 $alias = $sibling['alias'];
555 * Validates a single query.
560 * @param array $query The single query. Passed by reference.
562 private function clean_query( &$query ) {
563 if ( empty( $query['taxonomy'] ) ) {
564 if ( 'term_taxonomy_id' !== $query['field'] ) {
565 $query = new WP_Error( 'Invalid taxonomy' );
569 // so long as there are shared terms, include_children requires that a taxonomy is set
570 $query['include_children'] = false;
571 } elseif ( ! taxonomy_exists( $query['taxonomy'] ) ) {
572 $query = new WP_Error( 'Invalid taxonomy' );
576 $query['terms'] = array_unique( (array) $query['terms'] );
578 if ( is_taxonomy_hierarchical( $query['taxonomy'] ) && $query['include_children'] ) {
579 $this->transform_query( $query, 'term_id' );
581 if ( is_wp_error( $query ) )
585 foreach ( $query['terms'] as $term ) {
586 $children = array_merge( $children, get_term_children( $term, $query['taxonomy'] ) );
589 $query['terms'] = $children;
592 $this->transform_query( $query, 'term_taxonomy_id' );
596 * Transforms a single query, from one field to another.
600 * @global wpdb $wpdb The WordPress database abstraction object.
602 * @param array $query The single query. Passed by reference.
603 * @param string $resulting_field The resulting field. Accepts 'slug', 'name', 'term_taxonomy_id',
604 * or 'term_id'. Default 'term_id'.
606 public function transform_query( &$query, $resulting_field ) {
609 if ( empty( $query['terms'] ) )
612 if ( $query['field'] == $resulting_field )
615 $resulting_field = sanitize_key( $resulting_field );
617 switch ( $query['field'] ) {
620 foreach ( $query['terms'] as &$term ) {
622 * 0 is the $term_id parameter. We don't have a term ID yet, but it doesn't
623 * matter because `sanitize_term_field()` ignores the $term_id param when the
626 $term = "'" . esc_sql( sanitize_term_field( $query['field'], $term, 0, $query['taxonomy'], 'db' ) ) . "'";
629 $terms = implode( ",", $query['terms'] );
631 $terms = $wpdb->get_col( "
632 SELECT $wpdb->term_taxonomy.$resulting_field
633 FROM $wpdb->term_taxonomy
634 INNER JOIN $wpdb->terms USING (term_id)
635 WHERE taxonomy = '{$query['taxonomy']}'
636 AND $wpdb->terms.{$query['field']} IN ($terms)
639 case 'term_taxonomy_id':
640 $terms = implode( ',', array_map( 'intval', $query['terms'] ) );
641 $terms = $wpdb->get_col( "
642 SELECT $resulting_field
643 FROM $wpdb->term_taxonomy
644 WHERE term_taxonomy_id IN ($terms)
648 $terms = implode( ',', array_map( 'intval', $query['terms'] ) );
649 $terms = $wpdb->get_col( "
650 SELECT $resulting_field
651 FROM $wpdb->term_taxonomy
652 WHERE taxonomy = '{$query['taxonomy']}'
653 AND term_id IN ($terms)
657 if ( 'AND' == $query['operator'] && count( $terms ) < count( $query['terms'] ) ) {
658 $query = new WP_Error( 'Inexistent terms' );
662 $query['terms'] = $terms;
663 $query['field'] = $resulting_field;