WP_Date_Query::get_sql_for_query( array $query, int $depth ): array

Generates SQL clauses for a single query array.

Description

If nested subqueries are found, this method recurses the tree to produce the properly nested SQL.

Parameters

$queryarrayrequired
Query to parse.
$depthintoptional
Number of tree levels deep we currently are.
Used to calculate indentation. Default 0.

Return

array Array containing JOIN and WHERE SQL clauses to append to a single query array.
  • join string
    SQL fragment to append to the main JOIN clause.
  • where string
    SQL fragment to append to the main WHERE clause.

Source

protected function get_sql_for_query( $query, $depth = 0 ) {
	$sql_chunks = array(
		'join'  => array(),
		'where' => array(),
	);

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

	$indent = '';
	for ( $i = 0; $i < $depth; $i++ ) {
		$indent .= '  ';
	}

	foreach ( $query as $key => $clause ) {
		if ( 'relation' === $key ) {
			$relation = $query['relation'];
		} elseif ( is_array( $clause ) ) {

			// This is a first-order clause.
			if ( $this->is_first_order_clause( $clause ) ) {
				$clause_sql = $this->get_sql_for_clause( $clause, $query );

				$where_count = count( $clause_sql['where'] );
				if ( ! $where_count ) {
					$sql_chunks['where'][] = '';
				} elseif ( 1 === $where_count ) {
					$sql_chunks['where'][] = $clause_sql['where'][0];
				} else {
					$sql_chunks['where'][] = '( ' . implode( ' AND ', $clause_sql['where'] ) . ' )';
				}

				$sql_chunks['join'] = array_merge( $sql_chunks['join'], $clause_sql['join'] );
				// This is a subquery, so we recurse.
			} else {
				$clause_sql = $this->get_sql_for_query( $clause, $depth + 1 );

				$sql_chunks['where'][] = $clause_sql['where'];
				$sql_chunks['join'][]  = $clause_sql['join'];
			}
		}
	}

	// Filter to remove empties.
	$sql_chunks['join']  = array_filter( $sql_chunks['join'] );
	$sql_chunks['where'] = array_filter( $sql_chunks['where'] );

	if ( empty( $relation ) ) {
		$relation = 'AND';
	}

	// Filter duplicate JOIN clauses and combine into a single string.
	if ( ! empty( $sql_chunks['join'] ) ) {
		$sql['join'] = implode( ' ', array_unique( $sql_chunks['join'] ) );
	}

	// Generate a single WHERE clause with proper brackets and indentation.
	if ( ! empty( $sql_chunks['where'] ) ) {
		$sql['where'] = '( ' . "\n  " . $indent . implode( ' ' . "\n  " . $indent . $relation . ' ' . "\n  " . $indent, $sql_chunks['where'] ) . "\n" . $indent . ')';
	}

	return $sql;
}

Changelog

VersionDescription
4.1.0Introduced.

User Contributed Notes

You must log in before being able to contribute a note or feedback.