Automattic\WooCommerce\Internal\DataStores\Orders

OrdersTableQuery::build_query │ private │ WC 1.0

Builds the final SQL query to be run.

Method of the class: OrdersTableQuery{}

Returns

null. Nothing (null).

Usage

// private - for code of main (parent) class only
$result = $this->build_query(): void;

OrdersTableQuery::build_query() OrdersTableQuery::build query code ^{WC 10.3.6}

private function build_query(): void {
	$this->maybe_remap_args();

	// Field queries.
	if ( ! empty( $this->args['field_query'] ) ) {
		$this->field_query = new OrdersTableFieldQuery( $this );
		$sql               = $this->field_query->get_sql_clauses();
		$this->join        = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
		$this->where       = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
	}

	// Build query.
	$this->process_date_args();
	$this->process_orders_table_query_args();
	$this->process_operational_data_table_query_args();
	$this->process_addresses_table_query_args();

	// Search queries.
	if ( ! empty( $this->args['s'] ) ) {
		$this->search_query = new OrdersTableSearchQuery( $this );
		$sql                = $this->search_query->get_sql_clauses();
		$this->join         = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
		$this->where        = $sql['where'] ? array_merge( $this->where, $sql['where'] ) : $this->where;
	}

	// Meta queries.
	if ( ! empty( $this->args['meta_query'] ) ) {
		$this->meta_query = new OrdersTableMetaQuery( $this );

		$sql = $this->meta_query->get_sql_clauses();

		$this->join  = $sql['join'] ? array_merge( $this->join, $sql['join'] ) : $this->join;
		$this->where = $sql['where'] ? array_merge( $this->where, array( $sql['where'] ) ) : $this->where;

	}

	// Date queries.
	if ( ! empty( $this->args['date_query'] ) ) {
		$this->date_query = new \WP_Date_Query( $this->args['date_query'], "{$this->tables['orders']}.date_created_gmt" );
		$this->where[]    = substr( trim( $this->date_query->get_sql() ), 3 ); // WP_Date_Query includes "AND".
	}

	$this->process_orderby();
	$this->process_limit();

	$orders_table = $this->tables['orders'];

	// Group by is a faster substitute for DISTINCT, as long as we are only selecting IDs. MySQL don't like it when we join tables and use DISTINCT.
	$this->groupby[] = "{$this->tables['orders']}.id";
	$this->fields    = "{$orders_table}.id";
	$fields          = $this->fields;

	// JOIN.
	$join = implode( ' ', array_unique( array_filter( array_map( 'trim', $this->join ) ) ) );

	// WHERE.
	$where = '1=1';
	foreach ( $this->where as $_where ) {
		if ( strlen( $_where ) > 0 ) {
			$where .= " AND ({$_where})";
		}
	}

	// ORDER BY.
	$orderby = $this->orderby ? implode( ', ', $this->orderby ) : '';

	// LIMITS.
	$limits = '';

	if ( ! empty( $this->limits ) && count( $this->limits ) === 2 ) {
		list( $offset, $row_count ) = $this->limits;
		$row_count                  = -1 === $row_count ? self::MYSQL_MAX_UNSIGNED_BIGINT : (int) $row_count;
		$limits                     = 'LIMIT ' . (int) $offset . ', ' . $row_count;
	}

	// GROUP BY.
	$groupby = $this->groupby ? implode( ', ', (array) $this->groupby ) : '';

	$pieces = compact( 'fields', 'join', 'where', 'groupby', 'orderby', 'limits' );

	if ( ! $this->suppress_filters ) {
		/**
		 * Filters all query clauses at once.
		 * Covers the fields (SELECT), JOIN, WHERE, GROUP BY, ORDER BY, and LIMIT clauses.
		 *
		 * @since 7.9.0
		 *
		 * @param string[]         $clauses {
		 *     Associative array of the clauses for the query.
		 *
		 *     @type string $fields  The SELECT clause of the query.
		 *     @type string $join    The JOIN clause of the query.
		 *     @type string $where   The WHERE clause of the query.
		 *     @type string $groupby The GROUP BY clause of the query.
		 *     @type string $orderby The ORDER BY clause of the query.
		 *     @type string $limits  The LIMIT clause of the query.
		 * }
		 * @param OrdersTableQuery $query   The OrdersTableQuery instance (passed by reference).
		 * @param array            $args    Query args.
		 */
		$clauses = (array) apply_filters_ref_array( 'woocommerce_orders_table_query_clauses', array( $pieces, &$this, $this->args ) );

		$fields  = $clauses['fields'] ?? '';
		$join    = $clauses['join'] ?? '';
		$where   = $clauses['where'] ?? '';
		$groupby = $clauses['groupby'] ?? '';
		$orderby = $clauses['orderby'] ?? '';
		$limits  = $clauses['limits'] ?? '';
	}

	$groupby = $groupby ? ( 'GROUP BY ' . $groupby ) : '';
	$orderby = $orderby ? ( 'ORDER BY ' . $orderby ) : '';

	$this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits";

	if ( ! $this->suppress_filters ) {
		/**
		 * Filters the completed SQL query.
		 *
		 * @since 7.9.0
		 *
		 * @param string           $sql   The complete SQL query.
		 * @param OrdersTableQuery $query The OrdersTableQuery instance (passed by reference).
		 * @param array            $args  Query args.
		 */
		$this->sql = apply_filters_ref_array( 'woocommerce_orders_table_query_sql', array( $this->sql, &$this, $this->args ) );
	}

	$this->build_count_query( $fields, $join, $where, $groupby );
}