Automattic\WooCommerce\Internal\ProductAttributesLookup

Filterer::get_product_counts_query_using_lookup_table │ private │ WC 1.0

Get the query for counting products by terms using the product attributes lookup table.

Method of the class: Filterer{}

Returns

Array. An array of SQL query parts.

Usage

// private - for code of main (parent) class only
$result = $this->get_product_counts_query_using_lookup_table( $tax_query, $meta_query, $taxonomy, $term_ids );

$tax_query(WP_Tax_Query) (required)

The current main tax query.

$meta_query(WP_Meta_Query) (required)

The current main meta query.

$taxonomy(string) (required)

The attribute name to get the term counts for.

$term_ids(string) (required)

The term ids to include in the search.

Filterer::get_product_counts_query_using_lookup_table() Filterer::get product counts query using lookup table code ^{WC 10.5.0}

private function get_product_counts_query_using_lookup_table( $tax_query, $meta_query, $taxonomy, $term_ids ) {
	global $wpdb;

	$meta_query_sql = $meta_query->get_sql( 'post', $this->lookup_table_name, 'product_or_parent_id' );
	$tax_query_sql  = $tax_query->get_sql( $this->lookup_table_name, 'product_or_parent_id' );

	/**
	 * Filters the woocommerce_hide_out_of_stock_items option to override the default behavior in product filtering by attribute.
	 *
	 * @param bool $option_value The behavior configured in WooCommerce settings.
	 * @return bool The behavior to use in the catalog when product filtering by attribute.
	 *
	 * @since 9.5.0.
	 */
	$hide_out_of_stock = apply_filters( 'woocommerce_product_attributes_filterer_hide_out_of_stock', 'yes' === get_option( 'woocommerce_hide_out_of_stock_items' ) );
	$in_stock_clause   = $hide_out_of_stock ? ' AND in_stock = 1' : '';

	$query           = array();
	$query['select'] = 'SELECT COUNT(DISTINCT product_or_parent_id) as term_count, term_id as term_count_id';
	$query['from']   = "FROM {$this->lookup_table_name}";
	$query['join']   = "
		{$tax_query_sql['join']} {$meta_query_sql['join']}
		INNER JOIN {$wpdb->posts} ON {$wpdb->posts}.ID = {$this->lookup_table_name}.product_or_parent_id";

	$encoded_taxonomy = sanitize_title( $taxonomy );
	$term_ids_sql     = $this->get_term_ids_sql( $term_ids );
	$query['where']   = "
		WHERE {$wpdb->posts}.post_type IN ( 'product' )
		AND {$wpdb->posts}.post_status = 'publish'
		{$tax_query_sql['where']} {$meta_query_sql['where']}
		AND {$this->lookup_table_name}.taxonomy='{$encoded_taxonomy}'
		AND {$this->lookup_table_name}.term_id IN $term_ids_sql
		{$in_stock_clause}";

	if ( ! empty( $term_ids ) ) {
		$attributes_to_filter_by = \WC_Query::get_layered_nav_chosen_attributes();

		if ( ! empty( $attributes_to_filter_by ) ) {
			$and_term_ids = array();

			foreach ( $attributes_to_filter_by as $taxonomy => $data ) {
				if ( 'and' !== $data['query_type'] ) {
					continue;
				}
				$all_terms             = get_terms( $taxonomy, array( 'hide_empty' => false ) );
				$term_ids_by_slug      = wp_list_pluck( $all_terms, 'term_id', 'slug' );
				$term_ids_to_filter_by = array_values( array_intersect_key( $term_ids_by_slug, array_flip( $data['terms'] ) ) );
				$and_term_ids          = array_merge( $and_term_ids, $term_ids_to_filter_by );
			}

			if ( ! empty( $and_term_ids ) ) {
				$terms_count   = count( $and_term_ids );
				$term_ids_list = '(' . join( ',', $and_term_ids ) . ')';
				// The extra derived table ("SELECT product_or_parent_id FROM") is needed for performance
				// (causes the filtering subquery to be executed only once).
				$query['where'] .= "
					AND product_or_parent_id IN ( SELECT product_or_parent_id FROM (
						SELECT product_or_parent_id
						FROM {$this->lookup_table_name} lt
						WHERE is_variation_attribute=0
						{$in_stock_clause}
						AND term_id in {$term_ids_list}
						GROUP BY product_id
						HAVING COUNT(product_id)={$terms_count}
						UNION
						SELECT product_or_parent_id
						FROM {$this->lookup_table_name} lt
						WHERE is_variation_attribute=1
						{$in_stock_clause}
						AND term_id in {$term_ids_list}
						GROUP BY product_or_parent_id
						HAVING COUNT(DISTINCT term_id)={$terms_count}
					) temp )";
			}
		} else {
			$query['where'] .= $in_stock_clause;
		}
	} elseif ( $hide_out_of_stock ) {
		$query['where'] .= " AND {$this->lookup_table_name}.in_stock=1";
	}

	$search_query_sql = \WC_Query::get_main_search_query_sql();
	if ( $search_query_sql ) {
		$query['where'] .= ' AND ' . $search_query_sql;
	}

	$query['group_by'] = 'GROUP BY terms.term_id';
	$query['group_by'] = "GROUP BY {$this->lookup_table_name}.term_id";

	return $query;
}