WooCommerce product table category filter: logic and term order

A WooCommerce product table category filter turns every category a visitor selects into a condition of a WordPress taxonomy query and joins the selected categories with the logic you set for that filter element. Most surprises come from that logic, for example a subcategory that still shows every product of its parent. Below is how the filter builds its query, how to set it up for parent and child categories, how to change the order of terms in the drop-downs, and two related cases: the On Sale filter and filters during product imports.

How the category filter of a product table builds its query

Taxonomy filters are added in the Filter tab of a table. Every product taxonomy can be a filter element: Product categories, Product tags, every global attribute (pa_color, pa_size and so on) and custom product taxonomies. Click the gear button on an element to open its options (the popup is titled Table #12; filter field: product_cat). Four options matter here:

When the visitor selects terms, the plugin adds one clause to the products query for that element: the selected term IDs with the operator from Logic.

• IN (the default, the "OR" logic): products in at least one of the selected terms.
• AND: products that are in all selected terms at the same time.
• NOT IN: products that are in none of the selected terms. Useful for an "exclude" style filter.

Different filter elements are always combined with AND: a category, a color and a price range must all match. The filter also works only inside the products the table already shows, so the rules of the Predefinition tab apply first. The Logic option belongs to a table created in WOOT → Tables; a table drawn from shortcode attributes without a table ID always uses IN.

Parent and child categories in the category filter

Product categories are hierarchical, and in a taxonomy query WordPress treats a category as "this category and all its subcategories" by default, which is how the filter queries it too. This explains the typical complaint: a visitor selects Face and then Acne in a multi-select, and the table still shows everything from Face. With IN, the parent alone already matches every product of all its subcategories, so adding a subcategory cannot narrow anything.

Switching Logic to AND is rarely the answer for categories. WordPress expands each selected category with all of its subcategories and then requires a product to be in every one of them. Selecting a parent with AND therefore asks for products assigned to the parent and to every one of its subcategories, which is usually none. Two subcategories with AND show only the products assigned to both. There is one logic per filter element; you cannot use IN for some terms of a taxonomy and AND for others.

What works in practice:

• A single drop-down. Leave MultiSelect off. The visitor picks exactly one category, and choosing Acne replaces Face, so the table shows only Acne (and any categories below it).
• A multi-select with IN, for categories on the same level. Selecting Acne and Dry skin shows products from either of them. It only goes wrong when a parent is selected as well.
• A shorter list with Exclude or Include. The drop-down is built as a tree from the top level down, and a subcategory is listed only if its parent is listed. Excluding a parent removes its whole branch, and Include must contain the parent IDs too, otherwise their subcategories never appear. You find a term ID in Products → Categories (or Products → Attributes → Configure terms): hover over the term and read tag_ID= in the edit link.
• A separate table per section of the shop. If a page is about one parent category, restrict its table to that category in Predefinition and filter only its subcategories. See different tables for different categories.

Attributes and tags are flat, so AND works there as expected: Color with AND and the terms Red and Blue shows products that have both. A variable product "has" every attribute term that is listed in its attributes, regardless of which variation is in stock.

Term order in the filter drop-downs

The drop-downs list the terms of each level alphabetically by name, with subcategories under their parent and marked with a dash per level. Terms without published products are not listed. The order you set in WooCommerce, by dragging categories in Products → Categories or with Default sort order → Custom ordering of an attribute, is not used, and Include does not change the order either: it only decides which terms appear.

To list categories and attributes in the WooCommerce order, add this code. It changes only the queries that build the table filter, not other term lists on the site.

/*
 * WOOT table filter: list product categories and attribute terms in the
 * order set in WooCommerce instead of alphabetically.
 * Child theme functions.php or a code snippets plugin.
 */
add_filter('woot_profile_extend', function ($profile) {
    foreach ($profile as $key => $field) {
        if (!is_array($field) || empty($field['get_filter_draw_data']) || !taxonomy_exists($key)) {
            continue;
        }
        if ('product_cat' !== $key && !taxonomy_is_product_attribute($key)) {
            continue;
        }

        $draw = $field['get_filter_draw_data'];

        $profile[$key]['get_filter_draw_data'] = function ($table_id) use ($draw) {
            $shop_order = function ($query) {
                $taxonomy = current((array) $query->query_vars['taxonomy']);
                $query->query_vars['orderby'] = taxonomy_is_product_attribute($taxonomy) ? wc_attribute_orderby($taxonomy) : 'menu_order';
            };

            add_action('pre_get_terms', $shop_order, 5);
            $data = $draw($table_id);
            remove_action('pre_get_terms', $shop_order, 5);

            return $data;
        };
    }

    return $profile;
});

For attributes the code follows each attribute's own Default sort order (Custom ordering, Name, Name (numeric) or Term ID); categories follow their drag-and-drop order. It uses woot_profile_extend, the hook that exposes the table's fields. The term list is written into the page when the page is generated, so clear the page cache after adding the code.

The On Sale filter and variable products

The On Sale filter element is a switch. When it is on, the table shows only the products from WooCommerce's own list of products on sale. That list contains every product with an active sale price and the parent of every variation with an active sale price, so a variable product appears as soon as at least one of its variations is on sale.

WooCommerce keeps this list in a cache (the wc_products_onsale transient) and refreshes it when a product is saved and when a scheduled sale starts or ends. If sale prices were written straight into the database, for example by an import that bypasses WooCommerce, the filter can miss those products. Clear the cache in WooCommerce → Status → Tools → WooCommerce transients → Clear transients.

Filters during product imports

Everything in the filter is read from the database: the terms in the drop-downs (only terms that have products), and the limits of range sliders. While an import (WP All Import or any other) is running, products, terms and their counts change, so drop-downs can miss terms and slider limits can be wrong. This is not a filter error, and it ends with the import. Two practical points:

• Run large imports when traffic is low, or on a staging copy.
• The filter's term list is part of the page HTML. If you use a page cache, purge it after the import, otherwise visitors keep the list from the moment the page was cached.

If the filter still shows the wrong products

• Open the gear options of the element and check Logic: IN for "any of", AND for "all of", NOT IN for "none of".
• For categories, test with one subcategory selected and no parent: if that is right, the parent in the selection was the cause.
• Check the product itself: a product assigned to the parent category directly matches the parent, not a subcategory.
• Remember the Predefinition rules: the filter never shows products the table excludes.
• After changing terms or the order code, clear the page cache and reload logged out.

If the result still does not match the categories of your products, open a topic on the WOOT support forum with the table shortcode, the filter settings and an example product, and we will check it.