Automattic\WooCommerce\Internal\ProductFilters

FilterData::get_hierarchical_taxonomy_counts │ private │ WC 1.0

Get hierarchical taxonomy counts using optimized hierarchy data.

Method of the class: FilterData{}

No Hooks.

Returns

Array. Array of term_id => count pairs.

Usage

// private - for code of main (parent) class only
$result = $this->get_hierarchical_taxonomy_counts( $product_ids, $taxonomy_name );

$product_ids(string) (required)

Comma-separated list of product IDs.

$taxonomy_name(string) (required)

Original taxonomy name for hierarchy methods.

FilterData::get_hierarchical_taxonomy_counts() FilterData::get hierarchical taxonomy counts code ^{WC 10.3.3}

private function get_hierarchical_taxonomy_counts( string $product_ids, string $taxonomy_name ) {
	global $wpdb;

	// Step 1: Get all terms that have products in the filtered set (1 query).
	$taxonomy_escaped = esc_sql( wc_sanitize_taxonomy_name( $taxonomy_name ) );
	$base_terms_sql   = "
		SELECT DISTINCT tt.term_id, tt.term_taxonomy_id
		FROM {$wpdb->term_relationships} tr
		INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
		WHERE tr.object_id IN ( {$product_ids} )
		AND tt.taxonomy = '{$taxonomy_escaped}'
	";

	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	$base_terms = $wpdb->get_results( $base_terms_sql );

	if ( empty( $base_terms ) ) {
		return array();
	}

	// Step 2: Build hierarchy relationships using TaxonomyHierarchyData.
	$hierarchy_counts = array();
	$processed_terms  = array();

	// Process each base term and its ancestors.
	foreach ( $base_terms as $term ) {
		$term_id = (int) $term->term_id;

		// Count for the term itself and all its descendants.
		if ( ! isset( $hierarchy_counts[ $term_id ] ) ) {
			$descendants                  = $this->taxonomy_hierarchy_data->get_descendants( $term_id, $taxonomy_name );
			$descendants[]                = $term_id; // Include the term itself.
			$hierarchy_counts[ $term_id ] = $descendants;
		}

		// Get ancestors using hierarchy data.
		$ancestors = $this->taxonomy_hierarchy_data->get_ancestors( $term_id, $taxonomy_name );
		foreach ( $ancestors as $ancestor_id ) {
			if ( in_array( $ancestor_id, $processed_terms, true ) ) {
				continue;
			}

			$descendants   = $this->taxonomy_hierarchy_data->get_descendants( $ancestor_id, $taxonomy_name );
			$descendants[] = $ancestor_id; // Include the ancestor term itself.

			$hierarchy_counts[ $ancestor_id ] = $descendants;
			$processed_terms[]                = $ancestor_id;
		}
	}

	if ( empty( $hierarchy_counts ) ) {
		return array();
	}

	// Step 3: Execute batch counting using a single query with CASE statements.
	$count_cases = array();
	foreach ( $hierarchy_counts as $term_id => $term_ids ) {
		$term_ids_str  = implode( ',', array_map( 'absint', $term_ids ) );
		$count_cases[] = "COUNT(DISTINCT CASE WHEN tt.term_id IN ({$term_ids_str}) THEN tr.object_id END) as count_{$term_id}";
	}

	$batch_count_sql = '
		SELECT ' . implode( ', ', $count_cases ) . "
		FROM {$wpdb->term_relationships} tr
		INNER JOIN {$wpdb->term_taxonomy} tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
		WHERE tr.object_id IN ( {$product_ids} )
		AND tt.taxonomy = '{$taxonomy_escaped}'
	";

	// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
	$count_result = $wpdb->get_row( $batch_count_sql, ARRAY_A );

	if ( empty( $count_result ) ) {
		return array();
	}

	// Parse results back to term_id => count format.
	$final_counts = array();
	foreach ( $hierarchy_counts as $term_id => $term_ids ) {
		$count_key = "count_{$term_id}";
		if ( isset( $count_result[ $count_key ] ) && $count_result[ $count_key ] > 0 ) {
			$final_counts[ $term_id ] = absint( $count_result[ $count_key ] );
		}
	}

	return $final_counts;
}