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.