WooCommerce Filter by Attribute Block Not Working with HPOS

The Problem

I ran into this on a client store running WooCommerce 10.8 with HPOS enabled. They had a shop page using the Filter by Attribute block to let customers narrow products by colour and size. After the HPOS migration, every attribute the customer selected returned an empty product grid, even when the products clearly had those attributes assigned. The block UI updated, the URL query params updated (?filter_color=red&query_type_color=or), but the products grid showed "No products found."

WP_DEBUG was silent. The Site Health screen was clean. The products themselves had the attribute terms applied, confirmed in the product edit screen and via the REST API: /wp-json/wc/v3/products?attribute=pa_color&attribute_term=37 returned the expected list. The front-end filter block did not.

The frustrating part: switching HPOS sync off ("Compatibility mode" in WooCommerce > Settings > Advanced > Features) made filters work again immediately. Re-enable HPOS-only mode, filters broke again.

Why It Happens

The Filter by Attribute block does not query wp_posts and wp_term_relationships when HPOS is on. It uses the WooCommerce product attribute lookup table (wp_wc_product_attributes_lookup), a denormalised cache designed to make filter queries fast on large catalogs.

That table is populated by a background job (the "Regenerate Product Attributes Lookup Table" tool). On migration from legacy storage to HPOS-only mode, the lookup table does not always rebuild automatically. The table either:

1. Stays empty (lookup table never populated).
2. Has stale rows from before the migration where the product_or_parent_id column points at IDs the new layout has shifted.
3. Has rows but the taxonomy column does not match the active product attributes (renamed taxonomies never get pruned).

Any of those leaves the Filter block querying a table that returns no matches for (product_id, taxonomy, term_id) triples that exist in reality. Hence empty results.

WooCommerce's HPOS sync docs cover order data sync but say very little about the attribute lookup table, which trips up most stores migrating with active filter blocks.

The Fix

Three steps: confirm the table is the problem, rebuild it cleanly, and add a guard so future imports do not break filtering again.

Step 1: Confirm the lookup table is empty or stale. Run this from wp shell or a SQL query in your admin tool:

SELECT COUNT(*) FROM wp_wc_product_attributes_lookup;
SELECT COUNT(DISTINCT product_id) FROM wp_wc_product_attributes_lookup;
SELECT COUNT(*) FROM wp_term_relationships tr
  JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id
  WHERE tt.taxonomy LIKE 'pa_%';

If the first two counts are zero (or way below the third), the lookup table is the issue. On the client store, count one returned 0 while count three was 47,000+. Smoking gun.

Step 2: Rebuild the lookup table in batches. WooCommerce ships a tool but it queues a single Action Scheduler job that often runs out of memory on stores over 5,000 products. Trigger it from WP-CLI in batches:

wp wc product attribute_lookup regenerate --batch-size=200

If that command is not registered on your install, force it through the option flag and a manual action call:

wp option update woocommerce_attribute_lookup_enabled yes
wp eval 'do_action( "woocommerce_attribute_lookup_table_regenerate" );'

Then watch the Action Scheduler queue (/wp-admin/admin.php?page=wc-status&tab=action-scheduler&status=pending) until the woocommerce_run_product_attribute_lookup_regeneration_callback jobs finish. On the client store with 12,000 products this took about 6 minutes.

Step 3: Hook into bulk imports so they refresh the lookup. If you use a CSV importer or a sync from an ERP, those plugins write directly to wp_term_relationships and skip the lookup table. Hook into the WooCommerce importer to refresh per product:

add_action(
    'woocommerce_product_import_inserted_product_object',
    function ( $product, $data ) {
        if ( ! function_exists( 'wc_get_container' ) ) {
            return;
        }
        $store = wc_get_container()->get(
            Automattic\WooCommerce\Internal\ProductAttributesLookup\LookupDataStore::class
        );
        $store->create_data_for_product( $product );
    },
    10,
    2
);

Drop that in a small mu-plugin so it survives theme switches. Every product touched by the importer now also updates the lookup table.

Step 4: Verify with the front-end. Open the shop page, select a colour filter, watch the network tab. The Store API request to /wp-json/wc/store/v1/products?filter_color=red should now return products instead of an empty array. The Filter by Attribute block updates the grid and the counts in the sidebar.

For very large stores (50,000+ products), schedule a weekly nightly rebuild. Periodic catalog edits in the admin sometimes miss rows, and a recurring rebuild keeps things consistent:

if ( ! wp_next_scheduled( 'qasim_rebuild_attr_lookup' ) ) {
    wp_schedule_event( time(), 'weekly', 'qasim_rebuild_attr_lookup' );
}
add_action( 'qasim_rebuild_attr_lookup', function () {
    do_action( 'woocommerce_attribute_lookup_table_regenerate' );
} );

The Lesson

HPOS migrations are advertised as order-storage changes but they touch a lot more, and the product attribute lookup table is the most common collateral damage. If your filter blocks went silent after enabling HPOS, the lookup table is empty or stale. Rebuild it with WP-CLI in batches and add an import hook to keep it fresh.

If your WooCommerce store broke a customer-facing feature after an HPOS migration and the fix is not obvious, that is the kind of work I do. See my services. For a related WooCommerce filter performance issue, see WooCommerce product filters slow on large catalogs.