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{}
Hooks from the method
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 11.0.1
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 ) {
$offset = (int) ( $this->limits[0] ?? 0 );
$row_count = (int) ( $this->limits[1] ?? 0 );
if ( -1 === $row_count ) {
// For "unlimited" (-1) queries, mirror WP_Query's nopaging behavior and
// omit the LIMIT clause. When an offset is specified, MySQL requires a
// row count, so emit PHP_INT_MAX — portable across MySQL (well below
// its unsigned bigint max) and SQLite (its signed 64-bit max).
if ( $offset > 0 ) {
$limits = 'LIMIT ' . $offset . ', ' . PHP_INT_MAX;
}
} else {
$limits = 'LIMIT ' . $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 ) : '';
// Performance note: simplify the query to allow the query optimizer to select a more efficient execution plan. As of
// version 10.9, this logic is implemented here as alternative changes above are getting flagged by regression analysis.
if ( '' === $join && "{$orders_table}.id" === $fields ) {
$groupby = '';
}
$this->sql = "SELECT $fields FROM $orders_table $join WHERE $where $groupby $orderby $limits";
$filtered_sql = $this->sql;
if ( ! $this->suppress_filters ) {
/**
* Filters the completed SQL query.
*
* Note: queries left unmodified by this filter may later be rewritten for performance (see
* OrdersTableStatusUnionQuery), in which case the SQL received here is not the SQL that ends up
* being executed. Returning a modified query from this filter disables any such rewrite.
*
* @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.
*/
$filtered_sql = apply_filters_ref_array( 'woocommerce_orders_table_query_sql', array( $this->sql, &$this, $this->args ) );
}
if ( $filtered_sql === $this->sql ) {
// On large HPOS stores this multi-status, date-ordered query can get a slow plan (scanning millions of
// rows); rewriting it as a UNION of single-status queries lets the type_status_date index serve each
// branch. Only attempted when no 'woocommerce_orders_table_query_sql' callback changed the query. See
// OrdersTableStatusUnionQuery.
$status_union_sql = ( new OrdersTableStatusUnionQuery( $this ) )->get_sql(
compact( 'fields', 'join', 'where', 'groupby', 'orderby', 'limits' ),
$this->suppress_filters
);
$filtered_sql = $status_union_sql ?? $this->sql;
}
$this->sql = $filtered_sql;
$this->build_count_query( $fields, $join, $where, $groupby );
}