Mismatch between filter data count and actual results in FSE classic templates

[sunyatasattva]

Describe the bug

When adding a Filter by Attribute block in an FSE template, the count shown in parenthesis next to the filter may mismatch with the actual result. This is likely because the Filter block uses the Store API to fetch its data, while the classic template reloads the page and actually uses the WordPress query, and somehow the results of these two queries are different.

This also affects the Product Query block which inherits the query from the template (see #7382)

To reproduce

Steps to reproduce the behavior with WooCommerce sample data:

1. Add a “Filter by Attribute” block and a “Filter by Price” to the Product Catalog template.
2. Go to the front-end.
3. Select the attribute “Blue” and a price range between $10–18
4. Notice that the data count says (0), but the Classic Template shows one product.

You can notice that, if you add the “All Products” block, the results are consistent between the filters and what's shown, because both sources are the Store API.

Possible solution

We might want to transition the filters to SSR sooner rather than later. As with the proposed approach by Frontity, we are going to hydrate them, and actually get the query results as virtual DOM from the back-end, bypassing the Store API.

This introduces the difficulty on how to migrate them to SSR while still supporting the “All Products” block: whether we need to create alternative blocks, or what other solution we might find.

Screenshots

[nefeline]

While working on this issue for the past two days alongside @gigitux , I started to study the relationship between product attributes and the product meta and have a few suggestions to share.

I believe the biggest complexity here lies in the fact that there's a need to support a combination of multiple attributes and product metas, coming from different filter blocks, into a single query. The way get_attribute_counts is currently structured is not providing us with a solid base to guarantee that all of these combinations are consistently covered.

One way to solve this problem would be by adopting more atomic SQL queries for each one of the specified filters and their conditions (either Any or All) that are later on used to feed a final "parent query" that summarizes and consolidates the results. Below there's a working example of how these queries could be structured and incorporated into our codebase (the data used on this example comes from the Woo sample-products):

# Format final result (placing zero on null values)
SELECT coalesce(term_count, 0) as term_count, attributes.term_id as term_count_id
FROM (
         # Fetch all possible attribute values for a taxonomy.
         SELECT DISTINCT term_id
         FROM wp_wc_product_attributes_lookup
         WHERE taxonomy = 'pa_color') as attributes # 'pa_color' here corresponds to the filter by attribute color block.
         LEFT JOIN (

    # Count the products by attribute.
    SELECT COUNT(product_attribute_lookup.product_id) as term_count, product_attribute_lookup.term_id
    FROM wp_wc_product_attributes_lookup product_attribute_lookup
             INNER JOIN wp_posts posts
                        ON posts.ID = product_attribute_lookup.product_id

    # Ensure only published products are included.
    WHERE posts.post_type IN ('product', 'product_variation')
      AND posts.post_status = 'publish'

      # This is where the filter conditions are controlled.

      # The next segment corresponds to an "Any" (or) condition for attributes.
      # The filtered taxonomies in this example are blue (22) and green (23).
      # {start_condition_any_query}
      AND product_attribute_lookup.product_or_parent_id IN (
        SELECT product_or_parent_id
        FROM wp_wc_product_attributes_lookup
        WHERE taxonomy = 'pa_color'
          AND term_id IN (22, 23))
      # {end_condition_any_query}

      # The next segment corresponds to an "All" (and) condition for attributes.
      # The filtered taxonomies in this example are medium (26) and small (27)
      # {start_condition_all_query}
      AND product_attribute_lookup.product_or_parent_id IN (
        SELECT product_or_parent_id
        FROM wp_wc_product_attributes_lookup
        WHERE taxonomy = 'pa_size'
          AND term_id IN (26, 27)
        GROUP BY product_or_parent_id
        HAVING count(DISTINCT term_id) >= 2) # <- number of elements in the "term_id" IN operator
      # {end_condition_all_query}

      # The next segment corresponds to a product meta filter.
      # The filtered meta in this example is the price with a max value of 18.
      # Other possible valid values are the stock and the rating.
      # {start_product_meta_query}
      AND product_attribute_lookup.product_id IN (
        SELECT product_meta_lookup.product_id
        FROM wp_wc_product_meta_lookup product_meta_lookup
        WHERE product_meta_lookup.max_price <= 18.000000)
      # {end_product_meta_query}

    GROUP BY product_attribute_lookup.term_id
) summarize ON attributes.term_id = summarize.term_id

As a long-term improvement (not necessarily related to this issue), in addition to better structuring the queries, there's also room for performance optimizations, such as ensuring the queries are triggered only once and implementing caching.

[sunyatasattva]

This is a great rundown @nefeline ! Thank you for that. It seems that there is much room for optimizing such complex queries that we are going to end up with the Product Query block in combination with the filters. Which class is currently handling the SQL queries? Is it us, or are we using a WooCommerce/WordPress-provided API? Would it be possible to achieve such atomic results by using the abstractions already provided?

[nefeline]

Hey @sunyatasattva , thank you! Regarding your questions:

Which class is currently handling the SQL queries? Is it us, or are we using a WooCommerce/WordPress-provided API?

Based on all info gathered so far, the SQL queries for the store filters currently are on:

woocommerce-blocks/src/StoreApi/Utilities/ProductQueryFilters.php

For the Product Attributes Lookup, within the core of WooCommerce, we have the following class:

woocommerce/src/Internal/ProductAttributesLookup/Filterer.php

Would it be possible to achieve such atomic results by using the abstractions already provided?

If we take a look at the query previously shared here, it can be divided into six segments:

• Segment 1: Formats final result
• Segment 2: Counts the products by attribute.
• Segment 3: Ensures only published products are included.
• Segment 4: Fetch product attributes using the "Any" (or) filter condition.
• Segment 5: Fetch product attributes using the "All" (and) filter condition.
• Segment 6: Fetch product meta filter. Required for fetching info regarding max/min prices, stock and ratings.

If we abstract get_filtered_term_product_counts as proposed by @gigitux on #35730, we could rely on this method for fetching the information that here is currently represented by segments 4 and 5:

# Segment 4: Fetch product attributes using the "Any" (or) filter condition.
SELECT product_or_parent_id
        FROM wp_wc_product_attributes_lookup
        WHERE taxonomy = 'pa_color'
          AND term_id IN (22, 23))

 # Segment 5: Fetch product attributes using the "All" (and) filter condition.
SELECT product_or_parent_id
        FROM wp_wc_product_attributes_lookup
        WHERE taxonomy = 'pa_size'
          AND term_id IN (26, 27)
        GROUP BY product_or_parent_id
        HAVING count(DISTINCT term_id) >= 2) # <- number of elements in the "term_id" IN operator

Additionally, I would recommend having a method in place that doesn't return the count for these attribute filter conditions, as get_filtered_term_product_counts currently does: this way we can easily compare the query results between different attributes and count them only in the end. In other words, in the core of WooCommerce we could have a new get_filtered_term_product method.

When it comes to fetching data for the product meta filters (for prices, stock and ratings), I believe it would be interesting for us to have another method (which could be called get_filtered_product_meta, for example), for returning the values we need, as exemplified on segment 6:

# Segment 6: Fetch product meta filter. Required for fetching info regarding max/min prices, stock and ratings.
SELECT product_meta_lookup.product_id
        FROM wp_wc_product_meta_lookup product_meta_lookup
        WHERE product_meta_lookup.max_price <= 18.000000)

Note: in the spirits of relying on pre-existing methods for this purpose, the search_products method within woocommerce/includes/data-stores/class-wc-product-data-store-cpt.php seems to a good candidate for this purpose as well, although it requires a bit more of exploration to confirm.

Last but not least: Segments 1, 2 and 3 represent the "macro" query that manipulates the data received from the product meta and attribute queries (segments 4, 5 and 6). This macro query is the one responsible for ensuring the returned values are compared, counted and correctly formatted, and it is the one that in this proposed scenario represents the updated version of our get_attribute_counts

# Format final result (placing zero on null values)
SELECT coalesce(term_count, 0) as term_count, attributes.term_id as term_count_id
FROM (
         # Fetch all possible attribute values for a taxonomy.
         SELECT DISTINCT term_id
         FROM wp_wc_product_attributes_lookup
         WHERE taxonomy = 'pa_color') as attributes # 'pa_color' here corresponds to the filter by attribute color block.
         LEFT JOIN (

    # Count the products by attribute.
    SELECT COUNT(product_attribute_lookup.product_id) as term_count, product_attribute_lookup.term_id
    FROM wp_wc_product_attributes_lookup product_attribute_lookup
             INNER JOIN wp_posts posts
                        ON posts.ID = product_attribute_lookup.product_id

    # Ensure only published products are included.
    WHERE posts.post_type IN ('product', 'product_variation')
      AND posts.post_status = 'publish'

      # This is where the filter conditions are controlled.

      # The next segment corresponds to an "Any" (or) condition for attributes.
      # The filtered taxonomies in this example are blue (22) and green (23).
      # {start_condition_any_query}
      AND product_attribute_lookup.product_or_parent_id IN (
        # SEGMENT 4
      # {end_condition_any_query}

      # The next segment corresponds to an "All" (and) condition for attributes.
      # The filtered taxonomies in this example are medium (26) and small (27)
      # {start_condition_all_query}
      AND product_attribute_lookup.product_or_parent_id IN (
        # SEGMENT 5
      # {end_condition_all_query}

      # The next segment corresponds to a product meta filter.
      # The filtered meta in this example is the price with a max value of 18.
      # Other possible valid values are the stock and the rating.
      # {start_product_meta_query}
      AND product_attribute_lookup.product_id IN (
        # SEGMENT 6
      # {end_product_meta_query}

    GROUP BY product_attribute_lookup.term_id
) summarize ON attributes.term_id = summarize.term_id

I would also suggest renaming get_attribute_counts as this method is not only counting the results based on attributes, but product metas as well (if multiple filters are used in conjunction).

[gigitux]

Thanks for your extended answer! 🙇

When it comes to fetching data for the product meta filters (for prices, stock and ratings), I believe it would be interesting for us to have another method (which could be called get_filtered_product_meta, for example), for returning the values we need, as exemplified on segment 6:

Theoretically, the method that WC Core will accept as a parameter a WP_Meta_Query instance. In this way, the WC Core will take care of segment 6 too. (woocommerce/woocommerce#35730)

[nefeline]

Anytime 🙌 !

Theoretically, the method that WC Core will accept as a parameter a WP_Meta_Query instance. In this way, the WC Core will take care of segment 6 too. (woocommerce/woocommerce#35730)

With get_filtered_term_product_counts receiving WP_Meta_Query as a param, then I believe another scenario we can explore is having the "macro query" (which corresponds to the segments 1, 2, and 3 described here) running on get_filtered_term_product_counts directly, providing all product meta and attributes (from segments 4, 5 and 6) as parameters instead of just the product meta (the WP_Meta_Query instance).

Alternatively, we can ensure get_filtered_term_product_counts fetches this info from other separate methods (e.g., a get_filtered_product_meta and a get_filtered_product_attribute method, based on the user-defined filter params), ensuring a better separation of concerns.

In other words, I believe keeping the fetch of attributes and product meta separate from the comparison, format, and count queries can help us to keep a leaner codebase moving forward.

[ned-bs]

Also, I want to report similar issue.
If I add legacy widgets it works OK.
But if I add block base widgets, when I enter shirts category, filters still shows results from other categories.
Let say I have 5 shirts with blue.
When I enter shirts category, filter shows Blue (17).
I have total 17 products with blue color but some of them are jeans, shoes etc.
Again legacy filters are working OK.

[nefeline]

Hi folks! This issue was solved on #8599: to be released on WooCommerce Blocks 10.1.0