Automattic\WooCommerce\Admin\API\Reports\Orders\Stats

DataStore::orders_stats_sql_filter() │ protected │ WC 1.0

Updates the totals and intervals database queries with parameters used for Orders report: categories, coupons and order status.

Method of the class: DataStore{}

No Hooks.

Return

null. Nothing (null).

Usage

// protected - for code of main (parent) or child class
$result = $this->orders_stats_sql_filter( $query_args );

$query_args(array) (required)

Query arguments supplied by the user.

DataStore::orders_stats_sql_filter() DataStore::orders stats sql filter code ^{WC 8.7.0}

protected function orders_stats_sql_filter( $query_args ) {
	// phpcs:ignore Generic.Commenting.Todo.TaskFound
	// @todo Performance of all of this?
	global $wpdb;

	$from_clause        = '';
	$orders_stats_table = self::get_db_table_name();
	$product_lookup     = $wpdb->prefix . 'wc_order_product_lookup';
	$coupon_lookup      = $wpdb->prefix . 'wc_order_coupon_lookup';
	$tax_rate_lookup    = $wpdb->prefix . 'wc_order_tax_lookup';
	$operator           = $this->get_match_operator( $query_args );

	$where_filters = array();

	// Products filters.
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$product_lookup,
		'product_id',
		'IN',
		$this->get_included_products( $query_args )
	);
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$product_lookup,
		'product_id',
		'NOT IN',
		$this->get_excluded_products( $query_args )
	);

	// Variations filters.
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$product_lookup,
		'variation_id',
		'IN',
		$this->get_included_variations( $query_args )
	);
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$product_lookup,
		'variation_id',
		'NOT IN',
		$this->get_excluded_variations( $query_args )
	);

	// Coupons filters.
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$coupon_lookup,
		'coupon_id',
		'IN',
		$this->get_included_coupons( $query_args )
	);
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$coupon_lookup,
		'coupon_id',
		'NOT IN',
		$this->get_excluded_coupons( $query_args )
	);

	// Tax rate filters.
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$tax_rate_lookup,
		'tax_rate_id',
		'IN',
		implode( ',', $query_args['tax_rate_includes'] )
	);
	$where_filters[] = $this->get_object_where_filter(
		$orders_stats_table,
		'order_id',
		$tax_rate_lookup,
		'tax_rate_id',
		'NOT IN',
		implode( ',', $query_args['tax_rate_excludes'] )
	);

	// Product attribute filters.
	$attribute_subqueries = $this->get_attribute_subqueries( $query_args );
	if ( $attribute_subqueries['join'] && $attribute_subqueries['where'] ) {
		// Build a subquery for getting order IDs by product attribute(s).
		// Done here since our use case is a little more complicated than get_object_where_filter() can handle.
		$attribute_subquery = new SqlQuery();
		$attribute_subquery->add_sql_clause( 'select', "{$orders_stats_table}.order_id" );
		$attribute_subquery->add_sql_clause( 'from', $orders_stats_table );

		// JOIN on product lookup.
		$attribute_subquery->add_sql_clause( 'join', "JOIN {$product_lookup} ON {$orders_stats_table}.order_id = {$product_lookup}.order_id" );

		// Add JOINs for matching attributes.
		foreach ( $attribute_subqueries['join'] as $attribute_join ) {
			$attribute_subquery->add_sql_clause( 'join', $attribute_join );
		}
		// Add WHEREs for matching attributes.
		$attribute_subquery->add_sql_clause( 'where', 'AND (' . implode( " {$operator} ", $attribute_subqueries['where'] ) . ')' );

		// Generate subquery statement and add to our where filters.
		$where_filters[] = "{$orders_stats_table}.order_id IN (" . $attribute_subquery->get_query_statement() . ')';
	}

	$where_filters[] = $this->get_customer_subquery( $query_args );
	$refund_subquery = $this->get_refund_subquery( $query_args );
	$from_clause    .= $refund_subquery['from_clause'];
	if ( $refund_subquery['where_clause'] ) {
		$where_filters[] = $refund_subquery['where_clause'];
	}

	$where_filters   = array_filter( $where_filters );
	$where_subclause = implode( " $operator ", $where_filters );

	// Append status filter after to avoid matching ANY on default statuses.
	$order_status_filter = $this->get_status_subquery( $query_args, $operator );
	if ( $order_status_filter ) {
		if ( empty( $query_args['status_is'] ) && empty( $query_args['status_is_not'] ) ) {
			$operator = 'AND';
		}
		$where_subclause = implode( " $operator ", array_filter( array( $where_subclause, $order_status_filter ) ) );
	}

	// To avoid requesting the subqueries twice, the result is applied to all queries passed to the method.
	if ( $where_subclause ) {
		$this->total_query->add_sql_clause( 'where', "AND ( $where_subclause )" );
		$this->total_query->add_sql_clause( 'join', $from_clause );
		$this->interval_query->add_sql_clause( 'where', "AND ( $where_subclause )" );
		$this->interval_query->add_sql_clause( 'join', $from_clause );
	}
}