Automattic\WooCommerce\Admin\API\Reports

DataStore::get_attribute_subqueries()protectedWC 1.0

Returns product attribute subquery elements used in JOIN and WHERE clauses, based on query arguments from the user.

Method of the class: DataStore{}

No Hooks.

Return

Array.

Usage

// protected - for code of main (parent) or child class
$result = $this->get_attribute_subqueries( $query_args );
$query_args(array) (required)
Parameters supplied by the user.

DataStore::get_attribute_subqueries() code WC 9.3.3

protected function get_attribute_subqueries( $query_args ) {
	global $wpdb;

	$sql_clauses           = array(
		'join'  => array(),
		'where' => array(),
	);
	$match_operator        = $this->get_match_operator( $query_args );
	$post_meta_comparators = array(
		'='  => 'attribute_is',
		'!=' => 'attribute_is_not',
	);

	foreach ( $post_meta_comparators as $comparator => $arg ) {
		if ( ! isset( $query_args[ $arg ] ) || ! is_array( $query_args[ $arg ] ) ) {
			continue;
		}
		foreach ( $query_args[ $arg ] as $attribute_term ) {
			// We expect tuples.
			if ( ! is_array( $attribute_term ) || 2 !== count( $attribute_term ) ) {
				continue;
			}

			$term_id = '';
			// If the tuple is numeric, assume these are IDs.
			if ( is_numeric( $attribute_term[0] ) && is_numeric( $attribute_term[1] ) ) {
				$attribute_id = intval( $attribute_term[0] );
				$term_id      = intval( $attribute_term[1] );

				// Invalid IDs.
				if ( 0 === $attribute_id || 0 === $term_id ) {
					continue;
				}

				// @todo: Use wc_get_attribute () instead ?
				$attr_taxonomy = wc_attribute_taxonomy_name_by_id( $attribute_id );
				// Invalid attribute ID.
				if ( empty( $attr_taxonomy ) ) {
					continue;
				}

				$attr_term = get_term_by( 'id', $term_id, $attr_taxonomy );
				// Invalid term ID.
				if ( false === $attr_term ) {
					continue;
				}

				$meta_key   = sanitize_title( $attr_taxonomy );
				$meta_value = $attr_term->slug;
			} else {
				// Assume these are a custom attribute slug/value pair.
				$meta_key   = esc_sql( $attribute_term[0] );
				$meta_value = esc_sql( $attribute_term[1] );
				$attr_term  = get_term_by( 'slug', $meta_value, $meta_key );
				if ( false !== $attr_term ) {
					$term_id = $attr_term->term_id;
				}
			}

			$join_alias       = 'orderitemmeta1';
			$table_to_join_on = "{$wpdb->prefix}wc_order_product_lookup";

			if ( empty( $sql_clauses['join'] ) ) {
				$sql_clauses['join'][] = "JOIN {$wpdb->prefix}woocommerce_order_items orderitems ON orderitems.order_id = {$table_to_join_on}.order_id";
			}

			// If we're matching all filters (AND), we'll need multiple JOINs on postmeta.
			// If not, just one.
			if ( 'AND' === $match_operator || 1 === count( $sql_clauses['join'] ) ) {
				$join_idx              = count( $sql_clauses['join'] );
				$join_alias            = 'orderitemmeta' . $join_idx;
				$sql_clauses['join'][] = "JOIN {$wpdb->prefix}woocommerce_order_itemmeta as {$join_alias} ON {$join_alias}.order_item_id = {$table_to_join_on}.order_item_id";
			}

			$in_comparator = '=' === $comparator ? 'in' : 'not in';

			// Add subquery for products ordered using attributes not used in variations.
			$term_attribute_subquery = "select product_id from {$wpdb->prefix}wc_product_attributes_lookup where is_variation_attribute=0 and term_id = %s";
			// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// phpcs:disable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
			$sql_clauses['where'][] = $wpdb->prepare(
				"
				( ( {$join_alias}.meta_key = %s AND {$join_alias}.meta_value {$comparator} %s ) or (
					{$wpdb->prefix}wc_order_product_lookup.variation_id = 0 and {$wpdb->prefix}wc_order_product_lookup.product_id {$in_comparator} ({$term_attribute_subquery})
				) )",
				$meta_key,
				$meta_value,
				$term_id,
			);
			// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			// phpcs:enable WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber
		}
	}

	// If we're matching multiple attributes and all filters (AND), make sure
	// we're matching attributes on the same product.
	$num_attribute_filters = count( $sql_clauses['join'] );

	for ( $i = 2; $i < $num_attribute_filters; $i++ ) {
		$join_alias            = 'orderitemmeta' . $i;
		$sql_clauses['join'][] = "AND orderitemmeta1.order_item_id = {$join_alias}.order_item_id";
	}

	return $sql_clauses;
}