Exploring Variation Filtering and Custom Tables

Many developers and merchants are likely familiar with a gap in WooCommerce’s functionality around filtering products based on their variations. This quirk makes it difficult for customers to find what they’re looking for quickly, which ultimately translates to lost sales and lower revenues for merchants running WooCommerce.

The WooCommerce Core team has been aware of this long-standing limitation and has attempted a few different solutions over the past few years. Due to a number of interrelated factors that include technical challenges and competing priorities, however, this particular issue, despite its importance, has needed to be deferred multiple times. Developers in our community have likewise come up with their own innovative solutions and workarounds over time, but the promise of out-of-the-box variations filtering in WooCommerce has remained unfulfilled.

Over the past several weeks, the WooCommerce Core team has put additional effort into an exploration of how to best implement the architectural changes that would allow improved filtering on variations. This post provides an overview of that exploration along with a glimpse at what the final solution may look like.

Why is this functionality difficult to implement in WooCommerce?

The difficulty in properly supporting variation filtering stems from the underlying architecture of WooCommerce and how it is integrated with the WordPress APIs. Product data is stored in the wp_post and wp_postmeta tables in WordPress. Because of this, querying variations for operations like product filters and stock availability checks can create a big bottleneck that hinders a store’s performance.

The prevailing solution to the variations issue, and similar issues stemming from the underlying data storage architecture, is to introduce custom database tables for WooCommerce data along with data stores that abstract the database queries into a simple CRUD interface. You can see an initial exploration of custom tables in the WooCommerce Product Tables feature plugin that was introduced in 2018.

Adding the custom tables and data stores is only one piece of what needs to be a diligent solution though. Transitioning to new core database tables would be a breaking change since many extensions expect data to be in the wp_post and wp_postmeta tables. Because WooCommerce is committed to preserving backwards compatibility, the introduction of custom tables requires a mechanism for synchronizing data between new and legacy tables. Likewise, solving this problem involves migrating the data in the old tables to the new ones on live stores that are processing orders.

New developments

Using the lessons learned from previous initiatives, such as the implementation of custom tables in Action Scheduler 3.0 and the introduction of lookup tables in WooCommerce 3.6, the core team has recently dived into a new exploration of variation filtering using lookup tables as a stepping stone toward custom tables. The result of this effort is a Pull Request on GitHub with a proof of concept that you can investigate and even test locally today.

About the proof of concept

Because it’s only intended as a proof of concept, the solution linked above requires you to manually create and populate the database tables yourself, but the Pull Request contains CSV files and scripts to help streamline this process for folks who want to test it out. The Pull Request also includes a breakdown of the logic used for populating the lookup table with various product attributes.

Most importantly, however, the proof of concept demonstrates an improved implementation of filtering for product attributes, including:

  • It correctly excludes products for which there is no stock of a particular variant.
  • If the “Hide out of stock items from the catalog” option is unset, products which match the specified filters will appear in the filtered results along with correct product counts in the widget, even if the matching variation is out of stock.
  • When filtering on multiple values for an attribute, the filter is applied with an OR operator for variable products and with an AND operator for simple products by default.
  • Developers have the ability to toggle the logical operator from AND to OR for attributes by modifying the query string (e.g. &query_type_color=or)
  • When filtering by multiple attributes of different types, the filter is applied with an AND operator, displaying only products that match the values for all filtered attributes (e.g. products that are pink and available in medium)

There is one quirk to be aware of, however, when filtering by multiple attributes. If a variable product has variations in stock which, together, match all of the filtered attributes, the product will be displayed even if no individual variation matches all of the specified attributes.

Proper variations filtering is an essential function in an eCommerce store, and the WooCommerce Core team is committed to implementing this functionality in a way that is scalable, conscientious, and backwards compatible. We’re eager to hear your feedback on the initial exploration and the proof of concept. Please share your thoughts with us in the comments below or reach out to us in the WooCommerce Community Slack. If you have questions, you can also reach out to @konamiman, who put this proof of concept together.

10 responses to “Exploring Variation Filtering and Custom Tables”

  1. Good info! I am grateful that core team always have backward compatibility as a priority, breaking merchant sites that rely on extensions while upgrading wc should be avoided at all costs! Thanks!

  2. Backwards compatibility comes with a cost. Product Tables was the most promising improvement back in 2018 that made us switch to Woo. I still think it should be implemented, since it will solve the Architectural problem (like having huge databases of 50K+ orders).

  3. krimskijhan Avatar

    Progress is impossible without breaking changes. Expectations of buyers are evolving every year and shop owners have to keep up with it. People should not be rewarded for being lazy.
    Switching to dedicated product tables is long awaited change while I doubt there’s any problem creating a migration script.

  4. lunasplendida Avatar

    This is a long waited feature. We are a fashion retailer and recently came to realize how fundamentally wrong is the way the filters work today. Unfortunately we are stuck with WC until its fixed but I’ve since never proposed WC for any e-commerce store featuring mainly variable products (shoes, clothes, underwear) simply because filters are essentially useless in that case. Thank you for commiting to address this issue. Looking forward to hearing more news.

  5. Standalone tables would greatly help with performance, would love to see that land as a high priority task. Guess it may also help with migrating changes between staging and live sites. Shame that project has languished for 3 years.

    For those looking FaceWP does a great job of filtering, with standalone tables for performance. https://facetwp.com/

  6. […] The WooCommerce Core team has begun exploring how to implement improved filtering for variations via custom database tables. […]

  7. Custom tables is a big next step to improve Woo! Bigger customers (many orders / products / variations) need this for some time.

  8. bikeblogitalia Avatar

    agree with @addedlovely
    woocommerce team has to engage more in performance development, stop to use all development effort in block, dashboard and other useless stuff and spend more time in the development of a good database, multilingual, multiwarehouse, regional pricing, graphql, pwa ecc…
    woocommerce can be the best ecommerce solution but at this moment the developers of woo seems to try to be a bad copy of shopify

  9. Guys, go on with this change!!. This is the most important missing piece in woocommerce. Don’t be afraid of no-compatibility, stablish a road map and everybody would be aligned. It is not only about filtering, it is about inventory control, about proper product management. This is what makes a product great. We have been waiting for this, and now is the time to start the road.

Leave a Reply

Your email address will not be published. Required fields are marked *