The filtering of variable products in WooCommerce has been broken for some time. The problem appears when there are variable products where some variations have stock and others don’t, and the “hide out of stock products from the catalog” option is set. What happens is that when an attribute is selected for filtering, all variable products having a variation that corresponds to that attribute will be displayed, even those not having stock for that variation (as long as at least one variation has stock).
Example: you sell shoes in two colors, black and blue. Black is out of stock but there’s stock for blue. You select “black” (but not “blue”) in the widget. The shoes will be included in the products list, but they shouldn’t.
This happens because the attributes that define variations are stored as regular product terms, as the regular attributes. The WooCommerce product search (which is just the WordPress post search after all) is not variation-aware when it comes to attributes, and upon finding that the attribute selected in the filter is used by the product and the product is in stock (a variable product is in stock if at least one of the variations is), it just includes the product in the catalog regardless of the stock status of the target variation.
We have been aware of the problem for some time, but the fix is not easy. We did an attempt in Woo 4.4, but we had to end up reverting it because it didn’t really work well and caused a number of issues.
Lookup tables to the rescue
During the last months we have been working on an alternative approach using a new lookup table. There’s a proof of concept pull request that details the table structure and the logic followed to populate it, but in a nutshell, there’s one table row for each combination of product (this includes individual variations) and attribute, including the stock status too. Then SQL queries against this table are used when rendering the catalog and the “filter by attribute” widgets.
The actual development of the feature has been divided in three pull requests in order to make it easier to implement and review:
- Creation and filling of the table
- Actual usage of the table for filtering
- Synchronization of the table data whenever a product is created, modified or deleted
WooCommerce 5.6 will include all those and thus the feature is ready to be used. However, given the involved complexity we have decided to initially make it an opt-in feature, that is, it needs to be explicitly enabled. If you want to give it a try, here’s what you need:
How to enable the feature
- First you need to put this code snippet somewhere so that WooCommerce runs it at initialization time (running it in the
woocommerce_inithook is fine):
wc_get_container() ->get(\Automattic\WooCommerce\Internal\ProductAttributesLookup\LookupDataStore::class) ->show_feature();
2. Once the snippet is in place go to the WooCommerce – Status – Tools page. You will see a new tool with the title “Create and fill product attributes lookup table”:
3. Click the “Create” button. This will create the lookup table and trigger a series of scheduled tasks that will fill it with the appropriate information for the products that exist in the database, 10 products every time. If you reload the page (but remove the
&action=regenerate_product_attributes_lookup_table from the URL first! ) you’ll see that the tool button is disabled and contains the number of products processed so far.
Once the process finishes you’ll see that the button is enabled again and has the label “Regenerate” (also it has a product selector):
Clicking “Regenerate” allows you to start over and repeat the full table creation and filling, or to regenerate the table data just for one product; if everything works as planned you should never need to use this under normal usage circumstances.
Additionally, once the table creation has started there will be a new entry, “Delete the product attributes lookup table”, that you can use to leave things as they were in the beginning. It’s safe to use this tool even if the table is still being filled.
4. The last step is to tell WooCommerce to actually use the table for filtering. To do that, go to WooCommerce – Settings – Products – Advanced, check the “Use the product attributes lookup table for catalog filtering” mark and save.
And with this, you’re all set: WooCommerce will use the new table to decide which products to show in the catalog, depending on the stock status of each product and on the attributes selected for filtering.
An important thing to note is that the new lookup table will be kept in sync (updated whenever a product is created, modified or deleted) as long as the lookup table exists, even if the “Enable table usage” setting is disabled (the setting is about reading the table for doing the filtering). This means that you can enable and disable the setting at will, without having to do a manual table regeneration even if product data changes while the setting is disabled.
Also note that if you regenerate the entire table by using the entries in the tools page (either by using “Regenerate”, or “Delete” and then “Create”) you’ll need to enable the table usage from the settings page again.
We expect to collect feedback from the feature testing performed by advanced users (this probably includes you if you are reading this) and once we consider that it’s ready to ship we’ll roll it out for general usage. This will imply removing the need for running the snippet to enable the feature, removing the need to manually trigger the table creation (turning that process into a regular table migration instead) and enabling the lookup table usage by default (with the option to disable it via setting still present, though); how exactly we’ll do that (i.e. all these changes in one go or incrementally) is something we haven’t decided yet. But for now, it’s time for some testing, for which we thank you all in advance.
As a final note, please remember that you can use the WooCommerce beta tester plugin as a helper tool if you want to give a try to prerelease versions of WooCommerce.