WP_Tax_Query::get_sql_for_clause() │ public │ WP 4.1.0

Generates SQL JOIN and WHERE clauses for a "first-order" query clause.

Method of the class: WP_Tax_Query{}

No Hooks.

Return

Array. Array containing JOIN and WHERE SQL clauses to append to a first-order query.

Usage

$WP_Tax_Query = new WP_Tax_Query();
$WP_Tax_Query->get_sql_for_clause( $clause, $parent_query );

$clause(array) (required) (passed by reference — &)

Query clause (passed by reference).

$parent_query(array) (required)

Parent query array.

Notes

• Global. wpdb. $wpdb The WordPress database abstraction object.

Changelog

WP_Tax_Query::get_sql_for_clause() WP Tax Query::get sql for clause code ^{WP 6.5.2}

public function get_sql_for_clause( &$clause, $parent_query ) {
	global $wpdb;

	$sql = array(
		'where' => array(),
		'join'  => array(),
	);

	$join  = '';
	$where = '';

	$this->clean_query( $clause );

	if ( is_wp_error( $clause ) ) {
		return self::$no_results;
	}

	$terms    = $clause['terms'];
	$operator = strtoupper( $clause['operator'] );

	if ( 'IN' === $operator ) {

		if ( empty( $terms ) ) {
			return self::$no_results;
		}

		$terms = implode( ',', $terms );

		/*
		 * Before creating another table join, see if this clause has a
		 * sibling with an existing join that can be shared.
		 */
		$alias = $this->find_compatible_table_alias( $clause, $parent_query );
		if ( false === $alias ) {
			$i     = count( $this->table_aliases );
			$alias = $i ? 'tt' . $i : $wpdb->term_relationships;

			// Store the alias as part of a flat array to build future iterators.
			$this->table_aliases[] = $alias;

			// Store the alias with this clause, so later siblings can use it.
			$clause['alias'] = $alias;

			$join .= " LEFT JOIN $wpdb->term_relationships";
			$join .= $i ? " AS $alias" : '';
			$join .= " ON ($this->primary_table.$this->primary_id_column = $alias.object_id)";
		}

		$where = "$alias.term_taxonomy_id $operator ($terms)";

	} elseif ( 'NOT IN' === $operator ) {

		if ( empty( $terms ) ) {
			return $sql;
		}

		$terms = implode( ',', $terms );

		$where = "$this->primary_table.$this->primary_id_column NOT IN (
			SELECT object_id
			FROM $wpdb->term_relationships
			WHERE term_taxonomy_id IN ($terms)
		)";

	} elseif ( 'AND' === $operator ) {

		if ( empty( $terms ) ) {
			return $sql;
		}

		$num_terms = count( $terms );

		$terms = implode( ',', $terms );

		$where = "(
			SELECT COUNT(1)
			FROM $wpdb->term_relationships
			WHERE term_taxonomy_id IN ($terms)
			AND object_id = $this->primary_table.$this->primary_id_column
		) = $num_terms";

	} elseif ( 'NOT EXISTS' === $operator || 'EXISTS' === $operator ) {

		$where = $wpdb->prepare(
			"$operator (
				SELECT 1
				FROM $wpdb->term_relationships
				INNER JOIN $wpdb->term_taxonomy
				ON $wpdb->term_taxonomy.term_taxonomy_id = $wpdb->term_relationships.term_taxonomy_id
				WHERE $wpdb->term_taxonomy.taxonomy = %s
				AND $wpdb->term_relationships.object_id = $this->primary_table.$this->primary_id_column
			)",
			$clause['taxonomy']
		);

	}

	$sql['join'][]  = $join;
	$sql['where'][] = $where;
	return $sql;
}