Skip to content
This repository has been archived by the owner on Feb 23, 2024. It is now read-only.

Filter data count mismatch > Create the get_attribute_and_meta_counts method #8563

Closed
nefeline opened this issue Feb 27, 2023 · 0 comments · Fixed by #8599
Closed

Filter data count mismatch > Create the get_attribute_and_meta_counts method #8563

nefeline opened this issue Feb 27, 2023 · 0 comments · Fixed by #8599
Assignees
Labels
block: filter by attribute Issues related to the Filter by Attribute block. type: bug The issue/PR concerns a confirmed bug.

Comments

@nefeline
Copy link
Contributor

nefeline commented Feb 27, 2023

To solve the filter data count mismatch problem as described in the epic woocommerce/woocommerce#42466 , we need to create a new method to be responsible for manipulating the data received from the product meta and attribute queries (segments 4, 5 and 6, to be developed on #8561 and #8562) and return the final count, as represented by segments 1, 2 and 3 of the main query:

# 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 - The output of the `get_filtered_product_terms` method
      # {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 - The output of the `get_filtered_product_terms` method
      # {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 - The output of the `get_filtered_product_metas` method
      # {end_product_meta_query}

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

This macro query is the one responsible for ensuring the returned values are compared, counted, and correctly formatted. It is the one that, in this scenario, represents the updated version of our get_attribute_counts.

The output of this function should be the same as the pre-existing get_attribute_counts: array termId=>count pairs

We should rely on pre-existing functionality from the core of Woo whenever possible and follow the best WordPress practices when structuring the new method and queries.

This method can be initially placed within the ProductQueryFilters class, with the possibility to be migrated upstream later on to the core of Woo (to be worked as part of a separate issue).

As soon as the implementation is complete, we should extensively test the filters on the FE with different combinations to confirm everything works as expected.

Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
block: filter by attribute Issues related to the Filter by Attribute block. type: bug The issue/PR concerns a confirmed bug.
Projects
None yet
1 participant