Automattic\WooCommerce\Internal\Admin\Reports

HposLegacyOrderReportQueryBuilder::build_query │ public │ WC 11.1.0

Build the SQL clauses for an HPOS-backed legacy order report query.

Method of the class: HposLegacyOrderReportQueryBuilder{}

No Hooks.

Returns

array<string,string>. SQL clauses keyed by select/from/join/where/group_by/order_by/limit.

Usage

$HposLegacyOrderReportQueryBuilder = new HposLegacyOrderReportQueryBuilder();
$HposLegacyOrderReportQueryBuilder->build_query( $args, $start_date, $end_date ): array;

$args(array) (required)

Parsed report arguments.

$start_date(int) (required)

Start date as a Unix timestamp.

$end_date(int) (required)

End date as a Unix timestamp.

Changelog

HposLegacyOrderReportQueryBuilder::build_query() HposLegacyOrderReportQueryBuilder::build query code ^{WC 11.1.2}

public function build_query( array $args, int $start_date, int $end_date ): array {
	// The class is resolved from the DI container as a shared instance: reset the
	// per-build caches so option-derived state can't leak between queries.
	$this->report_schema = null;
	$this->column_map    = null;
	// Only trust the range when the query is actually bounded by it: queries with
	// filter_range off (e.g. the sparklines) may still carry the report page's
	// selected dates, which can describe a different DST period than the rows
	// they fetch.
	$this->report_range = ( ! empty( $args['filter_range'] ) && $start_date > 0 && $end_date > 0 )
		? array( $start_date, (int) strtotime( '+1 DAY', $end_date ) )
		: null;

	$data                = $args['data'] ?? array();
	$where               = $args['where'] ?? array();
	$where_meta          = $args['where_meta'] ?? array();
	$group_by            = $args['group_by'] ?? '';
	$order_by            = $args['order_by'] ?? '';
	$limit               = $args['limit'] ?? '';
	$filter_range        = $args['filter_range'] ?? false;
	$order_types         = $args['order_types'] ?? wc_get_order_types( 'reports' );
	$order_status        = $args['order_status'] ?? array();
	$parent_order_status = $args['parent_order_status'] ?? false;

	$select = array();
	$joins  = array();

	foreach ( $data as $raw_key => $value ) {
		$part = $this->build_data_select_and_joins( $raw_key, $value );
		if ( null === $part ) {
			continue;
		}
		$select[] = $part['select'];
		$joins    = array_merge( $joins, $part['joins'] );
	}

	// The CPT path builds joins from `( $data + $where )`, so a `where` row carrying a
	// `type` gets its join even without a matching `data` entry (rows shadowed by a
	// `data` key are skipped, matching the array-union semantics). Such rows only ever
	// contribute joins, never SELECT columns.
	foreach ( $where as $raw_key => $value ) {
		if ( is_array( $value ) && ! array_key_exists( $raw_key, $data ) ) {
			$joins = array_merge( $joins, $this->resolve_type_joins( $raw_key, $value ) );
		}
	}

	if ( ! empty( $where_meta ) ) {
		foreach ( $where_meta as $value ) {
			if ( is_array( $value ) ) {
				$joins = array_merge( $joins, $this->build_where_meta_joins( $value ) );
			}
		}
	}

	if ( ! empty( $parent_order_status ) ) {
		$joins = array_merge( $joins, $this->build_parent_orders_join() );
	}

	$query           = array();
	$query['select'] = 'SELECT ' . implode( ',', $select );
	$query['from']   = 'FROM ' . OrderUtil::get_table_for_orders() . ' AS orders';
	$query['join']   = implode( ' ', $joins );
	$query['where']  = $this->build_where_clause( $order_types, $order_status, $parent_order_status, (bool) $filter_range, $start_date, $end_date );

	if ( ! empty( $where_meta ) ) {
		$query['where'] .= $this->build_where_meta_predicates( $where_meta );
	}

	if ( ! empty( $where ) ) {
		$query['where'] .= $this->build_where_predicates( $where );
	}

	if ( $group_by ) {
		$query['group_by'] = 'GROUP BY ' . $this->translate_legacy_sql_fragment( $group_by );
	}

	if ( $order_by ) {
		$query['order_by'] = 'ORDER BY ' . $this->translate_legacy_sql_fragment( $order_by );
	}

	if ( $limit ) {
		$query['limit'] = "LIMIT {$limit}";
	}

	return $query;
}