Automattic\WooCommerce\Internal\ProductAttributesLookup
Filterer::get_product_counts_query_using_lookup_table()
Get the query for counting products by terms using the product attributes lookup table.
Method of the class: Filterer{}
No Hooks.
Return
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 9.6.1
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' ); $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} ) 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; }