Commit 73380d341ad for woocommerce
commit 73380d341ad1c2388c85e4a36236d7c59d112a8d
Author: Vlad Olaru <vlad.olaru@automattic.com>
Date: Fri Sep 25 13:05:36 2026 +0300
Keep disabled variations out of the product attributes lookup table (#68482)
* fix(product-attributes-lookup): regenerate lookup data when a variation status changes
The lookup table update action was chosen from the changeset keys
catalog_visibility, attributes and the stock keys only. Disabling a
variation (the "Enabled" checkbox, which sets its status to private)
produced a changeset with just "status", which mapped to ACTION_NONE,
so the table was never touched.
Map a status change of a variation to ACTION_INSERT so its rows are
regenerated. Product status changes are still ignored: a product's
status isn't stored in the table, the catalog query handles it.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): create lookup rows for published variations only
Both row-creation paths included unpublished variations: the object
path read get_children(), which returns private variations too, and
the optimized path selected variations in any status. A disabled
variation therefore kept making its parent match attribute filters
and count towards terms the shop couldn't sell.
The table already encodes filterability on the write side for catalog
visibility (rows deleted) and stock (in_stock column). Treat the
variation status the same way: skip unpublished variations when
creating rows, in both paths. A regeneration of a now-private
variation ends up as a delete.
get_visible_children() is deliberately not used: with "Hide out of
stock items" on it also drops out-of-stock variations, which would
replace the in_stock column semantics with row absence.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-filters): invalidate filter counts after the lookup table update runs
The filter data cache was invalidated on woocommerce_after_product_object_save,
which fires right after the lookup table update is scheduled, not after
it runs. With direct updates off (the default) the table changes later,
in an Action Scheduler job, so a storefront request in between cached
counts from the stale table and nothing invalidated them afterwards.
Fire woocommerce_product_attributes_lookup_updated at the end of the
update callback and invalidate the filter data cache on it.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-filters): count attribute terms from the lookup table
Attribute counts aggregated term_relationships, i.e. the terms attached
to the parent product. A parent keeps a term attached however many of
its variations are disabled, so the Product Filters block (and the
legacy Filter by Attribute block, through the collection-data route)
kept listing terms only disabled variations carried, even though
selecting the term returned nothing sellable.
Aggregate the product attributes lookup table instead, keyed by the
parent id. It now only holds rows for published variations, so no join
on the posts table is needed. Apply the same in_stock rule the
filtering clauses apply when out-of-stock items are hidden, and key the
caches on that option so toggling it can't serve stale counts.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): purge lookup rows of already-disabled variations
Rows are now only written for published variations and a variation
status change refreshes them, but every existing site still carries
rows for variations that were disabled before, and nothing revisits
them: no status trigger existed, and a regeneration only runs when an
admin asks for it.
Delete those rows once, with a single indexed DELETE ... JOIN on the
posts table, and drop the cached counts derived from them.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* test(product-filters): cover disabled variations in every attribute filter consumer
Pin that the main product query, the classic layered-nav widget counts
and the Product Filters block clauses all stop matching a product for a
term once the only variation carrying it is disabled. None of their
code changed: the coverage shows the lookup table contents alone are
enough.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* chore(changelog): add entry for disabled variations in attribute filters
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): invalidate filter counts after a lookup table regeneration
The regenerator writes lookup rows by calling
create_data_for_product() directly, never run_update_callback(),
so the woocommerce_product_attributes_lookup_updated action never
fires for a regeneration.
Attribute filter counts now come from the lookup table and are
cached in transients with a one-day TTL keyed on the filter_data
transient version. Nothing invalidated that version when a
regeneration finished, so a manual Tools or CLI regeneration left
the counts stale for up to a day, exactly when the admin is
trying to repair the filter data. While the regeneration runs the
table is empty and the filter block hides itself, so the entries
cached in that window outlive the regeneration too.
Invalidate the filter data cache once when the table settles: at
the end of finalize_regeneration(), and after the single-product
path of the Tools page entry. Doing it per product instead would
throw the cache away on every batch of a full regeneration for no
extra correctness.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* chore(changelog): document the lookup table semantics change for developers
The branch already ships a merchant-facing fix entry, which says
what the store owner sees but nothing about the table contract
extensions read.
The change alters what wc_product_attributes_lookup holds and adds
a hook, so extensions that count variation rows or cache anything
derived from the table need to know before they hit it in the
wild.
Add a dev entry spelling out the narrowed row set, the one-shot
database update that purges the rows already written, and the new
action to invalidate on.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* test(product-filters): tighten hide-out-of-stock and Store API attribute count fixtures
Both tests exercise attribute counts now that the counts come from
the lookup table, and both were looser than they read.
The hide-out-of-stock test only asserted the out-of-stock red term
was gone, so a change that dropped every variation term while the
option is on would still pass. The Store API OR-branch request
filtered on the slug 'large', a term the rewritten fixture no
longer creates: it creates 'large-slug', so the request matched
nothing and the branch proved nothing about an OR query over
attributes that actually exist.
Assert that the two in-stock green variations still count, and
point the OR-branch request at the slug the fixture creates. The
identical literal in the schema test stays as it is, because that
test stubs the filter data provider and never reaches the
database.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* docs(product-attributes-lookup): clarify the mirrored in-stock rule and the update action
Three spots in the new code left a reader to reconstruct something
the code cannot say on its own.
FilterData::hide_out_of_stock_items() drives an `in_stock` rule
that has to match the one QueryClauses::add_attribute_clauses()
applies, and nothing said so, so a change to either side would
silently make the counts and the filtered results disagree. The
new update action's docblock did not say it fires unconditionally,
which matters to anyone deciding how eagerly to invalidate on it.
The migration's phpcs justification claimed table names cannot be
prepared, which is not true here: `%i` exists, and the reason to
interpolate is that the repo's own identifier-placeholder sniff
asks for it with trusted table names.
Say all three, and put the ProductStatus import back in
alphabetical order.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-filters): fall back to term counts while the lookup table is being rebuilt
Attribute filter counts now come from wc_product_attributes_lookup,
the only place that knows which variations are published.
DataRegenerator::initiate_regeneration() truncates that table and
refills it in Action Scheduler batches, which can take a long time
on a large catalog. The only thing it sets meanwhile is
woocommerce_attribute_lookup_enabled = no, which FilterData never
consulted, so every uncached request during a rebuild got empty or
partial counts and the Product Filters attribute block hid itself
for the whole rebuild. Before the move the counts came from
term_relationships and stayed complete.
Fall back to the terms assigned to the parent products while a
regeneration is in progress or was aborted, the same pattern
get_stock_status_counts() already uses while wc_product_meta_lookup
is being populated. Both queries move into small private helpers so
the branch stays readable. Counts computed during a rebuild are
cached like any other and are invalidated when the regeneration
finishes.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): invalidate filter counts after a single-product regeneration from the CLI
Filter counts now come from wc_product_attributes_lookup, so
DataRegenerator invalidates them after it writes rows: on
finalize_regeneration() and on the tools page's single-product
branch.
CLIRunner::regenerate_for_product_core() called
LookupDataStore::create_data_for_product() directly, so
`wp wc palt regenerate_for_product` wrote new rows without
clearing anything. A count cached before the repair stayed wrong
for up to a day, which is the opposite of what someone running
the repair expects.
Three call sites want the same "regenerate one product, then
invalidate", so give DataRegenerator a regenerate_for_product()
method that does both, share the invalidation with
finalize_regeneration() through a private helper, and route the
tools page and the CLI through it.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): invalidate layered nav counts after the lookup table update runs
The classic layered navigation widget caches its term counts in
wc_layered_nav_counts_<taxonomy> transients for a day, and those are
invalidated when a product is saved. With the default deferred lookup
updates, the table itself is written later, in a scheduled action.
A shop visit between the save and that scheduled action re-caches the
counts from the old lookup rows, and nothing invalidated them once the
new rows landed, so a newly enabled or disabled variation kept a wrong
count in the widget until the transient expired. The Product Filters
block cache had the same gap, which is what the new
woocommerce_product_attributes_lookup_updated action addressed for it.
Invalidate the widget counts on that action too, and after a
regeneration, since regenerating writes rows directly without going
through LookupDataStore::run_update_callback(). The regenerator helper
is renamed to say it clears both caches.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-filters): count from the lookup table only while its usage is enabled
The attribute counts fall back to the parent terms while the product
attributes lookup table is being rebuilt, so a half-filled table does
not make terms disappear from the filter blocks.
That fallback was picked from the two regeneration flags. An aborted
regeneration cleaned up with `wp wc palt abort_regeneration --cleanup`
clears both flags while leaving the table incomplete, so the counts
went back to reading a half-built table and the terms of the products
that were never processed vanished until another regeneration
completed.
Decide from the `woocommerce_attribute_lookup_enabled` option instead,
through Filterer::filtering_via_lookup_table_is_active(), the same
predicate the main product query uses. WooCommerce sets it to `no`
before truncating the table, keeps it `no` after an aborted
regeneration is cleaned up, and back to `yes` only once a regeneration
completes, so one check now covers the rebuild, the aborted cleanup and
an admin disabling the table from the Tools page or the CLI.
The PHPUnit install leaves the option off, so the Store API attribute
count fixture turns it on to keep exercising the lookup table path.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* refactor(product-attributes-lookup): announce regenerations through the lookup updated action
The Product Filters block cache and the classic layered nav counts
already listen to woocommerce_product_attributes_lookup_updated, via
CacheController::register() and WC_Cache_Helper::init().
Regenerations and the 11.2.0-3 migration re-listed those same two
invalidators by hand, so a third consumer of the lookup table would
have had to be registered in four places instead of one. The
hand-rolled calls also bypassed CacheController::need_cleanup():
calling invalidate_filter_data_cache() directly writes a
never-expiring filter_data-transient-version transient, so the
migration made need_cleanup() permanently true even on stores that
never used the product filters.
Fire the action instead, with product id 0 when the whole table was
regenerated or cleaned up, and document that value on the hook.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* refactor(product-attributes-lookup): let the data store own the table usage predicate
FilterData::get_attribute_counts() resolved Filterer from the
container only to call filtering_via_lookup_table_is_active(), a
one-line read of the 'woocommerce_attribute_lookup_enabled' option.
Filterer builds the posts_clauses for the classic product query, so
depending on it just to read that option pulled in a class the
counts have nothing to do with.
Move the option read to LookupDataStore::usage_is_enabled(), which
already owns the table and the rest of its option-backed state, and
let Filterer delegate to it. FilterData now asks the data store
directly and passes the instance it resolved into the lookup table
query builder, which had been resolving it from the container a
second time.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-filters): key the filter data cache on the lookup table usage
The attribute counts read either the lookup table or the terms
assigned to the parent products, depending on
'woocommerce_attribute_lookup_enabled', but the filter data transient
key did not include that option. Turning the table usage off (`wp wc
palt disable`, or the Status - Tools page) therefore left counts
computed from the table cached for up to a day, and turning it back
on left the term counts cached the same way.
Add the option to the transient key, the same way
'woocommerce_hide_out_of_stock_items' is already keyed. The cached
product ids do not depend on the option, so their key is unchanged.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* refactor(product-attributes-lookup): reuse the published variation ids in the optimized path
create_data_for_product_cpt_core() stated the published-only rule
twice: the opening UNION query selects the variations with
post_status = 'publish' into $variation_ids, and the "variations
defined" query then re-derived the same set with a correlated
subquery on the posts table.
Two copies of one rule can drift apart, and the subquery re-reads
posts the method has already read. Interpolate the ids the first
query produced instead. In the variation branch $variation_ids holds
that variation, and for a variable product it holds the published
children, so the set matched by the subquery is the one the list now
carries and the query returns the same rows.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* test(store-api): enable the lookup table in the test setup instead of the product fixture
create_filterable_size_product() wrote
'woocommerce_attribute_lookup_enabled' itself, so the two tests that
call it enabled the table usage without saying so, and any test added
later would have had to call the fixture to get the same environment.
Move the option write to setUp(), where the rest of the per-test
environment is prepared, and leave the helper to create the product.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): register the lookup cleanup under the 11.2.0 update key
The cleanup was registered under a suffixed 11.2.0-3 key, following the
11.2.0-1 and 11.2.0-2 keys already on trunk, on the assumption that a
11.2.0 prerelease had shipped. It has not: there is no release branch,
tag or beta, and the release lead asked for all 11.2.0 migrations to
share the 11.2.0 key. A suffixed key also collides with another open
PR that registers its own migration under 11.2.0-3.
Move the callback under 11.2.0 and rename it to the 1120 prefix the
rest of that bucket uses. Sites already stamped 11.2.0 from nightly
builds skip the whole bucket and need the cleanup run by hand or a
lookup table regeneration; every released store runs it on upgrade.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* chore(phpstan): drop the resolved Filterer data_store baseline entry
Filterer::filtering_via_lookup_table_is_active() now reads the injected
data store, so the baselined "property is never read, only written"
error no longer exists and the PHPStan CI job fails on the unmatched
ignore. Remove the entry; the baseline only shrinks.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* refactor(product-attributes-lookup): announce lookup updates from the data store
The 'woocommerce_product_attributes_lookup_updated' action was fired
from four places. Only the one in run_update_callback carried the
docblock; the three outside LookupDataStore each repeated a "this
action is documented in" reference and a phpcs suppression for the
missing @since.
That put the hook name, its argument order and the suppression in
three files that do not own the table. Appending an argument, which
is the additive path the compatibility rules point to, would mean
four edits, and any site missed would keep firing the old shape.
The linter asking for a suppression at each of them was the signal.
Move the firing into LookupDataStore::announce_table_updated(), which
holds the docblock and the phpcs concern once, and have the callers
use it. The docblock also stops presenting the no-op firing as a
guarantee: it now tells listeners not to assume a row changed, which
leaves room to skip the announcement for updates that change nothing
without rewriting a published contract.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): delete the unpublished variation rows in batches
The 11.2.0 cleanup deleted every lookup row of an unpublished
variation in one multi-table DELETE joined against wp_posts, with no
cursor and no bound. MySQL does not accept LIMIT on a multi-table
delete, so the statement could not simply be capped.
wc_product_attributes_lookup is one of the largest tables on a
variation-heavy store, holding a row per variation and variation
attribute, and every filtered catalogue page reads it. Locking all
matching rows at once is the wrong shape there. Worse, a callback
that exceeds the request time limit never returns, so the 11.2.0 key
is never marked done and the next run starts the same query from
scratch: on a large store the update cannot converge. The migration
directly above this one, in the same release, already avoids this.
Select up to 250 unpublished variations past a stored cursor, delete
their rows by product_id, and return true until they run out, the way
wc_update_11202_reset_refund_returning_customer_markers does. Driving
from wp_posts makes the batch bounded by the selective side and lets
the query use the post_type and post_status index instead of scanning
the lookup table. The is_variation_attribute predicate goes away with
it: rows keyed on a variation id are always variation attribute rows,
so matching on the id alone selects the same set. The action now
fires on the final pass, once the table is consistent.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* perf(product-filters): invalidate the layered nav counts of the updated product only
The listener added for the lookup table update discarded the product
id the action hands it and queued a delete of the layered nav counts
transient for every attribute taxonomy in the store.
Those transients hold up to 1000 cached count results each, and every
one of them cost an aggregate query over the lookup table to produce.
The action also fires for stock updates, so every quantity edit that
reaches the lookup table, from the product editor, the REST API, an
import or an inventory sync, scheduled a store-wide flush. A store
with a few dozen attributes threw away tens of thousands of cached
results per edit, to reflect a change that can only affect the
attributes of one product. It also widened what the save-time path
deliberately keeps narrow: WC_Product_Data_Store_CPT::clear_caches()
invalidates only the product's own attribute keys.
Take the product id and resolve the taxonomies from the product, its
parent's included, since a variation carries only the attributes that
define it while the counts are cached against the parent's full set.
Fall back to every taxonomy when no product is passed, which is the
whole-table regeneration and cleanup case that does warrant a sweep.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* refactor(product-attributes-lookup): read the table usage flag through the data store
LookupDataStore::usage_is_enabled() was introduced as the definition
of "table usage is on" and adopted in Filterer and FilterData, but
CLIRunner still compared get_option( 'woocommerce_attribute_lookup_enabled' )
against 'yes' in four places.
The method's docblock now describes when the flag is off: during a
regeneration, after an aborted one is cleaned up, and when an admin
disabled it. Four copies of the raw option read sit outside that
definition, so a later change to it, such as also requiring the table
to exist, would leave the CLI reporting and gating on the old rule
while the storefront used the new one.
Call the data store, which CLIRunner already holds. The two writes to
the option stay as they are; only the reads move.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* refactor(product-filters): resolve the lookup data store through one accessor
FilterData reached for the same container singleton three ways: it
resolved it inline in get_attribute_counts(), resolved it again
inline in get_transient_key(), and threaded it through
get_attribute_counts_sql_from_lookup_table() as a parameter.
The parameter advertised a dependency the method does not really
have. It only needs the table name, the value is always recomputable
from the container the class already talks to, and typing it as
LookupDataStore made a private helper look like it could be called
with some other store, which it cannot. A fourth site needing the
store had no precedent to follow.
Add a private lookup_data_store() accessor next to
hide_out_of_stock_items(), which reads the option this one reads the
container for, and use it at all three sites. Constructor injection
would be more orthodox, but it would ripple into FilterDataProvider
and the tests that construct FilterData directly, so the accessor is
the smaller change with the same result.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* docs(product-filters): correct the attribute count fallback comment
The comment on the term relationship fallback said the counts come
from parent terms under "the same condition under which the main
product query filters through the table". That is true of the main
query, which reaches the table through Filterer and its usage check,
but it invited the reading that the fallback keeps the counts
complete whenever the table is out of use.
It does not. The id set these counts are computed over comes from
get_cached_product_ids(), which installs QueryClauses::add_query_clauses(),
and that reads the lookup table with no usage check at all. With
usage off and an attribute filter applied, the id set is already
empty, so the fallback returns nothing rather than complete counts.
It only yields complete counts while no attribute filter is active.
Say that, and note the other difference between the two branches:
only the lookup branch can honour woocommerce_hide_out_of_stock_items,
because parent term relationships carry no stock information. A test
already pins that divergence without naming it.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* test(product-filters): replace the duplicated fixture setup with helpers
Two pieces of fixture plumbing were repeated across the tests added
for disabled variations. Three tests in FilterDataTest opened with the
same four lines fetching the green and red pa_color terms and
asserting each is a WP_Term, so the subject of each test started on
its fifth line and one copy reordered the pair. Two tests then
repeated a byte-identical six-line block that disables a variation
through with_direct_product_attribute_lookup_updates() and checks its
lookup rows are gone.
Neither repetition says anything about what the tests verify, and the
reordered copy has to be diffed against the others to confirm it is
the same.
Add color_term_id() for the term lookup, so each test starts at its
real first step, and disable_variation() on the shared base class for
the disable-and-verify sequence. The row check stays inside the new
helper rather than being dropped: the count and filtering assertions
would catch a leaked row anyway, but the check localises the failure
to the write side instead of the read side.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* docs(performance): record the lookup table attribute count trade-off
The SQL query patterns guide named FilterData::get_attribute_counts as
the canonical example of "avoid the redundant posts join", and warned
against driving from the product-by-term cross-product in a lookup
table. That method no longer takes the recommended shape: counting
attributes from parent term relationships also counts terms that only
a disabled variation carries, which is the bug #42332 is about, so it
had to move to wc_product_attributes_lookup.
Left alone, the guide points at a query that no longer exists on the
default path, and the next reader has no way to tell whether the
switch was an oversight worth reverting.
Point the canonical example at Filterer, which still has the
recommended shape, and record what the trade costs when correctness
forces it. Measured on 2,000 variable products with 50 variations
each: 100k rows and about 23 ms through the lookup table against 20k
rows and about 4 ms through term relationships. No index recovers it,
because product_or_parent_id is the fourth column of the covering
index and neither the primary key nor a purpose-built index beats the
optimizer's own choice. The gap tracks variations per product and
inverts once row counts are comparable, so the lever is how often the
query runs, not how it is shaped.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* perf(product-attributes-lookup): skip announcing a stock update that changed no row
run_update_callback() announced every ACTION_UPDATE_STOCK pass through
woocommerce_product_attributes_lookup_updated, whether or not the
UPDATE it ran changed a row. The action is scheduled whenever a save
puts stock_quantity, stock_status or manage_stock in the changeset,
which the admin product editor, the REST API, CSV import and inventory
sync integrations do on every quantity write. When the quantity moves
without crossing the in-stock boundary, the in_stock column already
holds the right value, the UPDATE changes nothing, and both listeners
still flush: the filter data cache group is versioned out and the
layered nav counts of the product's attributes are deleted.
Order-line stock reductions do not reach this path. wc_update_product_stock()
writes the new quantity straight into the product's data rather than
its changeset, so save() schedules nothing unless the boundary is
crossed, and then the row does change.
Return the changed-row count from update_stock_status_for() and skip
the announcement when it is 0. wpdb connects without CLIENT_FOUND_ROWS
unless MYSQL_CLIENT_FLAGS says otherwise, so rows_affected counts rows
that changed, not rows that matched; a host that does set the flag only
announces more often, never less. ACTION_INSERT and ACTION_DELETE stay
unconditional: a regeneration that rewrites identical rows is still
announced, and the hook docblock now says so instead of promising an
announcement for every no-op.
Refs #42332
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
* fix(product-attributes-lookup): recheck the variation status when the cleanup deletes its rows
wc_update_1120_delete_unpublished_variation_lookup_rows() selects a
batch of unpublished variation ids from the posts table and then
deletes their lookup rows by id.
Between those two statements another request can re-enable one of
those variations. A variation status change is ACTION_INSERT, and
with direct updates on that runs inside the save, so by the time the
DELETE is issued the variation is published and has its rows back.
The DELETE, matching on the ids selected earlier, removes them, and
the filters stop offering the variation's terms until its next save.
Join the posts table in the DELETE and match on the current status,
so a variation republished since its batch was selected keeps its
rows. The batch is still bounded by the selected ids, so the join
touches at most 250 posts by primary key.
Refs #42332
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
* docs(performance): point the redundant posts join pattern at a query that has none
The SQL query patterns guide named Filterer's
get_product_counts_query_using_lookup_table as the canonical example
of "avoid the redundant posts join". That query joins wp_posts and
filters on post_type and post_status, because its ids come from the
lookup table rather than an upstream WP_Query, so it is the necessary
join the guide describes as Pattern B, not the redundant one.
A reader following the example would copy the join the pattern tells
them to drop.
Name FilterData's term-relationship counts query, which survives as
the fallback path of get_attribute_counts and has the recommended
shape, as the Pattern A example, and list the Filterer query under
Pattern B with the reason its join is required.
Refs #42332
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
* chore(phpstan): drop the resolved update_stock_status_for baseline entry
update_stock_status_for() now declares an int return type, so the
baselined "has no return type specified" error no longer occurs and
PHPStan fails on the unmatched ignore.
Remove the entry. A single-file analyse run does not report unmatched
baseline entries, which is why the earlier local check passed.
Refs #42332
Co-Authored-By: Claude Fable 5.1 <noreply@anthropic.com>
* fix(product-attributes-lookup): clear widget counts directly after the cleanup
The unpublished variation cleanup cleared the cached layered nav counts
by firing woocommerce_product_attributes_lookup_updated, whose
WC_Cache_Helper listener invalidated every attribute. After review the
PR is being reduced to the lookup table data fix, and that action goes
with the rest of the cache timing work.
The cleanup still has to clear those counts: the Filter Products by
Attribute widget caches them for a day and an upgrade does not flush
them, so the widget would keep offering the deleted terms. Call
WC_Cache_Helper::invalidate_attribute_count() for every attribute
taxonomy instead, which is what the listener did for a whole-table
change, and assert the cached counts are gone in the test.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): move the lookup cleanup to the 11.3.0 update
Trunk moved to 11.3.0-dev (#68712) and release/11.2 is cut, so this PR
ships in 11.3.0. WC_Install skips an update key once a site's database
version reaches it, so a store upgraded to 11.2.0 would never run a
callback added to the 11.2.0 key.
Register the cleanup under a new 11.3.0 key and rename the function,
its cursor option and its tests to the 1130 prefix, with @since 11.3.0.
The function moves below the 11.2.0 update functions to keep the file
in version order.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-filters): keep block attribute counts on parent terms
Review found that counting Product Filters block attribute terms from
the lookup table makes Store API collection-data counts disagree with
/products, which filters attributes through the parent tax_query and
never reads the table. Block results and counts come from several
engines that are being unified separately, so switching one of them
here trades one inconsistency for another.
Restore FilterData, CacheController and their tests, the
ProductCollectionData fixtures, and the performance skill notes that
documented the switch to their trunk versions. Classic themes stay
fixed: Filterer reads the lookup table for both the filtered results
and the widget counts.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* refactor(product-attributes-lookup): remove the lookup table updated action
The action let caches derived from the table be invalidated after a
deferred update ran instead of on product save. With the PR reduced to
the table's data nothing in core listens to it any more, and a new
public hook is a contract that is hard to take back.
Remove the action with LookupDataStore::announce_table_updated() and
usage_is_enabled(), the stock update skip that only saved
announcements, DataRegenerator::regenerate_for_product(), and the
WC_Cache_Helper listener, restoring CLIRunner, Filterer and the PHPStan
baseline to trunk. The save-time invalidation trunk already has stays.
Counts re-cached before a deferred update lands stay stale until they
expire, which trunk already does for stock changes.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* chore(changelog): scope the entries to the lookup table cleanup
The fix entry now names the classic consumers the change fixes, and
the dev entry drops the removed action and names the 11.3.0 update.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* test(product-attributes-lookup): cover draft, pending and trashed variations
The lookup table and its cleanup were only tested with disabled
(private) variations, while the change treats every status other than
publish as unpublished. Trunk's optimized creation path wrote rows for
draft and pending variations, and the cleanup selects every variation
whose status is not publish, including trashed ones.
Run the variable product and single variation creation tests for
private, draft and pending (plus trash for a single variation) on both
the object and the optimized access paths, and run the cleanup test for
private, draft, pending and trash.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* test(product-attributes-lookup): cover the cleanup batch boundary
The cleanup handles unpublished variations 250 at a time behind a
cursor option, but no test crossed a batch boundary, so an off-by-one
in the cursor or the limit would go unnoticed.
Seed 251 unpublished variations and one published variation among the
first batch's ids as bare posts and lookup rows, the rows the cleanup
reads, and assert each run: the first batch leaves only the published
variation and the 251st, the second leaves the published one, and an
empty third batch ends the migration and removes the cursor.
Refs #42332
Co-Authored-By: Claude Opus 5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): check the cleanup cursor write
The 11.3.0 lookup cleanup saved its batch cursor with an unchecked
update_option(). Deleting a variation's lookup rows does not publish
it, so without a saved cursor the next run selects the same batch
again. A write that keeps failing made the migration re-delete one
batch and reschedule itself forever.
Reuse the cursor handling the HPOS order-date repair already uses in
this file (#68772): write only when the stored cursor is behind, and
on a false return re-read the option uncached, so a cursor that a
concurrent run already saved counts as progress. Only a cursor that
still has not moved stops the migration, with a logged error. It falls
through to the shared tail rather than returning early, so the layered
nav counts cached from rows deleted by earlier batches are still
invalidated.
Refs #42332
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): re-read a first cursor past notoptions
A concurrent run that saves the same first cursor makes update_option()
return false. The re-read then still returned the default, because this
process had cached the option as missing in 'notoptions' before the
other run created it, and deleting the option's own cache key leaves
that entry in place. The run logged a false error and stopped.
Clear 'notoptions' before the re-read, and stop claiming the cursor only
moves forward: another process can overwrite a later cursor, which only
causes a rescan. The concurrency test now writes the row the way another
process would, and the stop test checks that cached counts are cleared.
Refs #42332
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
* chore(changelog): shorten the lookup table entries to the guidelines
The fix entry described the lookup table mechanism instead of what
merchants see, and the dev entry ran to about 470 characters, close to
the length AGENTS.md treats as a failing entry. Both bodies ship to the
public changelog.
Say what changes for merchants in the fix entry, and keep only the
change in which rows the table holds in the dev entry. The details stay
in the PR description.
Refs #42332
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
* docs(product-attributes-lookup): say when the cleanup stops for good
The migration's docblock only described the true return. WC_Install
treats false as done and never reruns the callback, so a failed SELECT,
a failed DELETE or a cursor that can't be saved ends the cleanup for
good. Two other migrations in this file already say so in their
docblocks; this one now does too.
Refs #42332
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): build rows for scheduled parents
With optimized updates on, regenerating a variable product that is
scheduled (future) or not saved yet (auto-draft) left it with no
lookup rows, or only the first full 100-row INSERT batches. The first
query only accepted the parent in publish, draft, pending or private,
but still matched its published variations through post_parent. The
parent was then processed as a variation of itself, and its rows
broke the INSERT with an empty in_stock value. Publishing never
rebuilt the rows: a parent's status change triggers no update.
This was already the case on trunk, which kept the INSERT batches
before the failing one. Since this branch reuses the ids from the
first query for the variation attributes, the first batch fails and
nothing is kept, which is how review found it.
Accept future and auto-draft parents, as the non-optimized path
already does through wc_get_product(). The rows stay out of the
shop until the product is published, because Filterer only counts
products with post_status 'publish'.
Refs #42332
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
* fix(product-attributes-lookup): skip a parent in an unlisted status
The optimized regeneration still treats a parent as a variation of
itself when its status is outside the accepted list (trash, or a
status another plugin registers) and it has published variations
matched through post_parent. It then builds an INSERT with an empty
in_stock value, which fails and logs an error on every regeneration.
WooCommerce trashes the variations with their parent, so this needs a
status written some other way, but the result should be no rows and
no error, not a failed query. When no parent row came back and none
of the returned rows is the product itself, delete its rows and
return, the same as for a product that no longer exists.
Refs #42332
Co-Authored-By: Claude Opus 5.5 (1M context) <noreply@anthropic.com>
---------
Co-authored-by: Claude Opus 5 (1M context) <noreply@anthropic.com>
diff --git a/plugins/woocommerce/changelog/fix-42332-lookup-table-disabled-variations b/plugins/woocommerce/changelog/fix-42332-lookup-table-disabled-variations
new file mode 100644
index 00000000000..4abd027954d
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-42332-lookup-table-disabled-variations
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Stop classic shop attribute filters from matching or counting terms that only disabled variations have.
diff --git a/plugins/woocommerce/changelog/fix-42332-lookup-table-disabled-variations-dev b/plugins/woocommerce/changelog/fix-42332-lookup-table-disabled-variations-dev
new file mode 100644
index 00000000000..11ffcb09e46
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-42332-lookup-table-disabled-variations-dev
@@ -0,0 +1,4 @@
+Significance: minor
+Type: dev
+
+Rows with `is_variation_attribute = 1` in `wc_product_attributes_lookup` now exist only for published variations.
diff --git a/plugins/woocommerce/changelog/fix-42332-lookup-table-scheduled-parents b/plugins/woocommerce/changelog/fix-42332-lookup-table-scheduled-parents
new file mode 100644
index 00000000000..ee079a3093f
--- /dev/null
+++ b/plugins/woocommerce/changelog/fix-42332-lookup-table-scheduled-parents
@@ -0,0 +1,4 @@
+Significance: patch
+Type: fix
+
+Keep new and scheduled variable products in attribute filters when optimized lookup table updates are on.
diff --git a/plugins/woocommerce/includes/class-wc-install.php b/plugins/woocommerce/includes/class-wc-install.php
index 24ee797294b..9328bcfb38f 100644
--- a/plugins/woocommerce/includes/class-wc-install.php
+++ b/plugins/woocommerce/includes/class-wc-install.php
@@ -362,6 +362,7 @@ class WC_Install {
),
'11.3.0' => array(
'wc_update_1130_set_legacy_variation_price_hash_option',
+ 'wc_update_1130_delete_unpublished_variation_lookup_rows',
),
);
diff --git a/plugins/woocommerce/includes/wc-update-functions.php b/plugins/woocommerce/includes/wc-update-functions.php
index 26c280f504a..1fbdda9ad2d 100644
--- a/plugins/woocommerce/includes/wc-update-functions.php
+++ b/plugins/woocommerce/includes/wc-update-functions.php
@@ -23,6 +23,7 @@ use Automattic\WooCommerce\Admin\Notes\Note;
use Automattic\WooCommerce\Admin\Notes\Notes;
use Automattic\WooCommerce\Database\Migrations\MigrationHelper;
use Automattic\WooCommerce\Enums\DefaultCustomerAddress;
+use Automattic\WooCommerce\Enums\ProductStatus;
use Automattic\WooCommerce\Enums\ProductStockStatus;
use Automattic\WooCommerce\Enums\ProductType;
use Automattic\WooCommerce\Internal\Admin\Marketing\MarketingSpecs;
@@ -4021,3 +4022,98 @@ function wc_update_1130_set_legacy_variation_price_hash_option() {
add_option( 'woocommerce_use_legacy_get_variations_price_hash', 'yes', '', true );
}
}
+
+/**
+ * Delete the product attributes lookup rows of variations that are not published.
+ *
+ * Disabled variations (status 'private') used to keep their lookup rows, so attribute filters offered and
+ * counted terms that only a disabled variation carried. Rows are now only written for published variations
+ * and a status change refreshes them, but rows written before that are never revisited.
+ *
+ * Runs in batches of unpublished variations, so the lookup table is never locked wholesale: it is one of the
+ * largest tables on a variation-heavy store, and every filtered catalogue page reads it. A database error, or a
+ * progress cursor that can't be saved, is logged and stops the migration without a retry.
+ *
+ * @since 11.3.0
+ *
+ * @return bool True when another batch is left to process, false when done or stopped.
+ */
+function wc_update_1130_delete_unpublished_variation_lookup_rows() {
+ global $wpdb;
+
+ $lookup_data_store = wc_get_container()->get( LookupDataStore::class );
+ if ( ! $lookup_data_store->check_lookup_table_exists() ) {
+ return false;
+ }
+
+ $last_id_option = 'woocommerce_update_1130_last_unpublished_variation_id';
+ $lookup_table = $lookup_data_store->get_lookup_table_name();
+
+ // Driving from the posts side keeps the batch bounded by unpublished variations, which are the selective
+ // set here, and lets the query use the post_type/post_status index instead of scanning the lookup table.
+ $variation_ids = $wpdb->get_col(
+ $wpdb->prepare(
+ "SELECT ID FROM {$wpdb->posts}
+ WHERE ID > %d AND post_type = 'product_variation' AND post_status != %s
+ ORDER BY ID ASC
+ LIMIT 250",
+ (int) get_option( $last_id_option, 0 ),
+ ProductStatus::PUBLISH
+ )
+ );
+
+ if ( '' === $wpdb->last_error && ! empty( $variation_ids ) ) {
+ $variation_ids = array_map( 'intval', $variation_ids );
+ $id_placeholders = implode( ', ', array_fill( 0, count( $variation_ids ), '%d' ) );
+
+ // Rows of a variation are always variation attribute rows, so matching on the id alone is enough. The
+ // status is checked again here: with direct updates on, a variation re-enabled since the batch was
+ // selected already has its rows back, and they must stay.
+ // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare -- The table name comes from the data store, and trusted table names are interpolated directly because that is what WooCommerceInternal.DB.IdentifierPlaceholder.Unguarded asks for; placeholders are generated per ID.
+ $deleted = $wpdb->query(
+ $wpdb->prepare(
+ "DELETE lookup FROM {$lookup_table} AS lookup
+ INNER JOIN {$wpdb->posts} AS posts ON posts.ID = lookup.product_id
+ WHERE lookup.product_id IN ( {$id_placeholders} ) AND posts.post_status != %s",
+ array_merge( $variation_ids, array( ProductStatus::PUBLISH ) )
+ )
+ );
+ // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare
+
+ if ( false !== $deleted ) {
+ // A concurrent run (the queue plus `wp wc update`) may already have saved this id or a later one, and update_option()
+ // reports that as false just like a failed write. It can also replace a later cursor with this one, which only makes a
+ // run reselect variations whose rows are already gone. The variations stay unpublished, so without a saved cursor the
+ // next run would pick the same batch again.
+ $cursor = end( $variation_ids );
+ if ( (int) get_option( $last_id_option, 0 ) >= $cursor || update_option( $last_id_option, $cursor, false ) ) {
+ return true;
+ }
+ // Re-read from the database. If the other run created the option after this process found it missing, the stale
+ // "missing" entry sits in 'notoptions', not under the option's own key.
+ wp_cache_delete( $last_id_option, 'options' );
+ wp_cache_delete( 'notoptions', 'options' );
+ if ( (int) get_option( $last_id_option, 0 ) >= $cursor ) {
+ return true;
+ }
+ wc_get_logger()->error(
+ 'Stopped deleting the lookup rows of unpublished variations: the progress cursor could not be saved.',
+ array( 'source' => 'wc_update_1130_delete_unpublished_variation_lookup_rows' )
+ );
+ }
+ }
+
+ if ( '' !== $wpdb->last_error ) {
+ wc_get_logger()->error(
+ sprintf( 'Stopped deleting the lookup rows of unpublished variations: %s', $wpdb->last_error ),
+ array( 'source' => 'wc_update_1130_delete_unpublished_variation_lookup_rows' )
+ );
+ }
+
+ delete_option( $last_id_option );
+
+ // The layered nav counts cached from the deleted rows would otherwise be served until they expire, a day later.
+ WC_Cache_Helper::invalidate_attribute_count( wc_get_attribute_taxonomy_names() );
+
+ return false;
+}
diff --git a/plugins/woocommerce/src/Internal/ProductAttributesLookup/LookupDataStore.php b/plugins/woocommerce/src/Internal/ProductAttributesLookup/LookupDataStore.php
index e62bdcd0829..b873e0d4568 100644
--- a/plugins/woocommerce/src/Internal/ProductAttributesLookup/LookupDataStore.php
+++ b/plugins/woocommerce/src/Internal/ProductAttributesLookup/LookupDataStore.php
@@ -5,6 +5,7 @@
namespace Automattic\WooCommerce\Internal\ProductAttributesLookup;
+use Automattic\WooCommerce\Enums\ProductStatus;
use Automattic\WooCommerce\Enums\ProductStockStatus;
use Automattic\WooCommerce\Enums\ProductType;
use Automattic\WooCommerce\Enums\CatalogVisibility;
@@ -140,7 +141,7 @@ class LookupDataStore {
return;
}
- $action = $this->get_update_action( $changeset );
+ $action = $this->get_update_action( $changeset, $this->is_variation( $product ) );
if ( self::ACTION_NONE !== $action ) {
$this->maybe_schedule_update( $product->get_id(), $action );
}
@@ -224,9 +225,10 @@ class LookupDataStore {
* Determine the type of action to perform depending on the received changeset.
*
* @param array|null $changeset The changeset received by on_product_changed.
+ * @param bool $is_variation True if the changed product is a variation.
* @return int One of the ACTION_ constants.
*/
- private function get_update_action( $changeset ) {
+ private function get_update_action( $changeset, bool $is_variation ) {
if ( is_null( $changeset ) ) {
// No changeset at all means that the product is new.
return self::ACTION_INSERT;
@@ -237,6 +239,7 @@ class LookupDataStore {
// Order matters:
// - The change with the most precedence is a change in catalog visibility
// (which will result in all data being regenerated or deleted).
+ // - Then a status change of a variation (data regenerated: unpublished variations get no rows).
// - Then a change in attributes (all data will be regenerated).
// - And finally a change in stock status (existing data will be updated).
// Thus these conditions must be checked in that same order.
@@ -250,6 +253,12 @@ class LookupDataStore {
}
}
+ // The "Enabled" checkbox of a variation toggles its status between 'publish' and 'private'.
+ // A product's own status isn't stored in the table, so it doesn't trigger anything.
+ if ( $is_variation && in_array( 'status', $keys, true ) ) {
+ return self::ACTION_INSERT;
+ }
+
if ( in_array( 'attributes', $keys, true ) ) {
return self::ACTION_INSERT;
}
@@ -453,12 +462,16 @@ class LookupDataStore {
}
/**
- * Create all the necessary lookup data for a given variation.
+ * Create all the necessary lookup data for a given variation. Unpublished variations get none.
*
* @param \WC_Product_Variation $variation The variation to create entries for.
* @throws \Exception Can't retrieve the details of the parent product.
*/
private function create_data_for_variation( \WC_Product_Variation $variation ) {
+ if ( ! $this->is_published_variation( $variation ) ) {
+ return;
+ }
+
$main_product = WC()->call_function( 'wc_get_product', $variation->get_parent_id() );
if ( false === $main_product ) {
// phpcs:ignore WordPress.Security.EscapeOutput.ExceptionNotEscaped
@@ -553,19 +566,33 @@ class LookupDataStore {
}
/**
- * Get the variations of a given variable product.
+ * Get the published variations of a given variable product.
+ *
+ * Unpublished (disabled) variations get no lookup data: they can't be bought, so they mustn't make
+ * their parent match an attribute filter or count towards a term.
*
* @param \WC_Product_Variable $product The product to get the variations for.
- * @return array An array of WC_Product_Variation objects.
+ * @return \WC_Product_Variation[]
*/
private function get_variations_of( \WC_Product_Variable $product ) {
- $variation_ids = $product->get_children();
- return array_map(
+ $variations = array_map(
function ( $id ) {
return WC()->call_function( 'wc_get_product', $id );
},
- $variation_ids
+ $product->get_children()
);
+
+ return array_filter( $variations, array( $this, 'is_published_variation' ) );
+ }
+
+ /**
+ * Check if a value is a published variation.
+ *
+ * @param mixed $product Product object, or false when the product couldn't be loaded.
+ * @return bool
+ */
+ private function is_published_variation( $product ): bool {
+ return $product instanceof \WC_Product_Variation && ProductStatus::PUBLISH === $product->get_status( 'edit' );
}
/**
@@ -873,8 +900,9 @@ class LookupDataStore {
)
);
- // * Obtain list of product variations, together with stock statuses; also get the product type.
+ // * Obtain list of published product variations, together with stock statuses; also get the product type.
// For a variation this will return just one entry, with type 'variation'.
+ // Scheduled and auto-draft products get data too, as in the non-optimized path; it's used only once they're published.
// Output: $product_ids_with_stock_status = associative array where 'id' is the key and values are the stock status (1 for "in stock", 0 otherwise).
// $variation_ids = raw list of variation ids.
// $is_variable_product = true or false.
@@ -887,7 +915,7 @@ class LookupDataStore {
left join {$wpdb->term_taxonomy} tt on tt.term_taxonomy_id=tr.term_taxonomy_id
left join {$wpdb->terms} t on t.term_id=tt.term_id
where p.post_type = 'product'
- and p.post_status in ('publish', 'draft', 'pending', 'private')
+ and p.post_status in ('publish', 'draft', 'pending', 'private', 'future', 'auto-draft')
and tt.taxonomy='product_type'
and t.name != 'exclude-from-search'
and p.id=%d
@@ -896,7 +924,7 @@ class LookupDataStore {
(select p.ID as id, p.post_parent as parent, m.meta_value as stock_status, 'variation' as product_type from {$wpdb->posts} p
left join {$wpdb->postmeta} m on p.id=m.post_id and m.meta_key='_stock_status'
where p.post_type = 'product_variation'
- and p.post_status in ('publish', 'draft', 'pending', 'private')
+ and p.post_status = 'publish'
and (p.ID=%d or p.post_parent=%d));
",
$product_id,
@@ -907,8 +935,8 @@ class LookupDataStore {
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$product_ids_with_stock_status = $wpdb->get_results( $sql, ARRAY_A );
if ( empty( $product_ids_with_stock_status ) ) {
- // The product has been deleted. The DELETE above only covers rows keyed by parent id,
- // delete_data_for also removes the rows of a deleted variation (keyed by product_id).
+ // The product has been deleted, or it's a variation that is no longer published. The DELETE above
+ // only covers rows keyed by parent id, delete_data_for also removes the rows keyed by product_id.
$this->delete_data_for( $product_id );
return;
}
@@ -916,6 +944,13 @@ class LookupDataStore {
$main_product_row = array_filter( $product_ids_with_stock_status, fn( $item ) => ProductType::VARIATION !== $item['product_type'] );
$is_variation = empty( $main_product_row );
+ // A product in a status not covered above (for example 'trash') still matches its variations through post_parent,
+ // but it isn't a variation itself, so it gets no data.
+ if ( $is_variation && ! in_array( $product_id, array_map( 'intval', array_column( $product_ids_with_stock_status, 'id' ) ), true ) ) {
+ $this->delete_data_for( $product_id );
+ return;
+ }
+
$main_product_id =
$is_variation ?
current( $product_ids_with_stock_status )['parent'] :
@@ -1004,15 +1039,19 @@ class LookupDataStore {
if ( ! $is_variation && ( ! $is_variable_product || empty( $variation_ids ) ) ) {
$variations_defined = array();
} else {
+ // The first query already selected the published variations, so the ids are reused here
+ // instead of deriving the same set again with a subquery on the posts table.
+ $variation_ids_list = implode( ',', array_map( 'intval', $variation_ids ) );
+
+ // phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- $variation_ids_list holds the integer ids this method computed.
$sql = $wpdb->prepare(
"select post_id as variation_id, substr(meta_key,11) as attribute, meta_value as slug from {$wpdb->postmeta}
- where post_id in (select ID from {$wpdb->posts} where (id=%d or post_parent=%d) and post_type = 'product_variation')
+ where post_id in ({$variation_ids_list})
and meta_key like %s
and meta_value != ''",
- $product_id,
- $product_id,
'attribute_pa_%'
);
+ // phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
$variations_defined = $wpdb->get_results( $sql, ARRAY_A );
$variations_defined = ArrayUtil::group_by_column( $variations_defined, 'variation_id' );
diff --git a/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php b/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php
index 3512d225c7a..3786d0c8b0e 100644
--- a/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php
+++ b/plugins/woocommerce/tests/php/includes/wc-update-functions-test.php
@@ -13,6 +13,7 @@ use Automattic\WooCommerce\Blocks\InboxNotifications;
use Automattic\WooCommerce\Blocks\Options as BlockOptions;
use Automattic\WooCommerce\Blocks\Utils\BlockTemplateUtils;
use Automattic\WooCommerce\Enums\OrderStatus;
+use Automattic\WooCommerce\Enums\ProductStatus;
use Automattic\WooCommerce\Internal\Features\FeaturesController;
use Automattic\WooCommerce\Internal\VariationGallery\Package as VariationGalleryPackage;
@@ -631,6 +632,304 @@ class WC_Update_Functions_Test extends \WC_Unit_Test_Case {
$this->assertSame( '0', $get_marker( $order->get_id() ), 'The order row marker should be left unchanged.' );
}
+ /**
+ * @testdox Migration deletes the lookup rows of unpublished variations and keeps every other row.
+ *
+ * @testWith ["private"]
+ * ["draft"]
+ * ["pending"]
+ * ["trash"]
+ *
+ * @param string $status The status of the variation that is not published.
+ */
+ public function test_wc_update_1130_delete_unpublished_variation_lookup_rows( string $status ): void {
+ global $wpdb;
+
+ include_once WC_ABSPATH . 'includes/wc-update-functions.php';
+
+ $product = WC_Helper_Product::create_variation_product();
+ $variation_ids = $product->get_children();
+ $this->assertGreaterThanOrEqual( 2, count( $variation_ids ) );
+
+ $unpublished_variation = wc_get_product( $variation_ids[0] );
+ $unpublished_variation->set_status( $status );
+ $unpublished_variation->save();
+
+ $lookup_table = $wpdb->prefix . 'wc_product_attributes_lookup';
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $wpdb->query( "DELETE FROM {$lookup_table}" );
+ $rows = array(
+ array( $product->get_id(), 0 ),
+ array( $variation_ids[0], 1 ),
+ array( $variation_ids[1], 1 ),
+ );
+ foreach ( $rows as list( $product_id, $is_variation_attribute ) ) {
+ $wpdb->insert(
+ $lookup_table,
+ array(
+ 'product_id' => $product_id,
+ 'product_or_parent_id' => $product->get_id(),
+ 'taxonomy' => 'pa_size',
+ 'term_id' => 1,
+ 'is_variation_attribute' => $is_variation_attribute,
+ 'in_stock' => 1,
+ ),
+ array( '%d', '%d', '%s', '%d', '%d', '%d' )
+ );
+ }
+
+ // Saving the variation queued its own invalidation, flush it so that only the migration's is observed.
+ WC_Cache_Helper::delete_transients_on_shutdown();
+ $counts_transient = 'wc_layered_nav_counts_pa_size';
+ set_transient( $counts_transient, array( 'query_hash' => array( 1 => 2 ) ) );
+
+ $batches = 0;
+ while ( wc_update_1130_delete_unpublished_variation_lookup_rows() ) {
+ ++$batches;
+ $this->assertLessThan( 10, $batches, 'The migration reschedules itself until every batch is done.' );
+ }
+
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $remaining = array_map( 'intval', $wpdb->get_col( "SELECT product_id FROM {$lookup_table}" ) );
+ $this->assertEqualsCanonicalizing( array( $product->get_id(), $variation_ids[1] ), $remaining );
+
+ WC_Cache_Helper::delete_transients_on_shutdown();
+ $this->assertFalse( get_transient( $counts_transient ), 'The layered nav counts cached from the deleted rows are invalidated.' );
+
+ $this->assertFalse(
+ get_option( 'woocommerce_update_1130_last_unpublished_variation_id' ),
+ 'The batch cursor is cleaned up once the migration is done.'
+ );
+ }
+
+ /**
+ * @testdox Migration keeps the rows of a variation that was published after its batch was selected.
+ */
+ public function test_wc_update_1130_delete_unpublished_variation_lookup_rows_rechecks_the_status_at_delete_time(): void {
+ global $wpdb;
+
+ include_once WC_ABSPATH . 'includes/wc-update-functions.php';
+
+ $product = WC_Helper_Product::create_variation_product();
+ $variation_id = $product->get_children()[0];
+ $variation = wc_get_product( $variation_id );
+ $variation->set_status( ProductStatus::PRIVATE );
+ $variation->save();
+
+ $lookup_table = $wpdb->prefix . 'wc_product_attributes_lookup';
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $wpdb->query( "DELETE FROM {$lookup_table}" );
+ $wpdb->insert(
+ $lookup_table,
+ array(
+ 'product_id' => $variation_id,
+ 'product_or_parent_id' => $product->get_id(),
+ 'taxonomy' => 'pa_size',
+ 'term_id' => 1,
+ 'is_variation_attribute' => 1,
+ 'in_stock' => 1,
+ ),
+ array( '%d', '%d', '%s', '%d', '%d', '%d' )
+ );
+
+ // With direct updates on, a save that re-enables the variation writes its rows back between the batch
+ // SELECT and the DELETE. Publishing it the moment the DELETE is issued reproduces that ordering.
+ $republished = false;
+ add_filter(
+ 'query',
+ function ( $query ) use ( &$republished, $lookup_table, $variation_id ) {
+ if ( ! $republished && str_starts_with( ltrim( $query ), 'DELETE' ) && str_contains( $query, $lookup_table ) ) {
+ $republished = true;
+ global $wpdb;
+ $wpdb->update( $wpdb->posts, array( 'post_status' => ProductStatus::PUBLISH ), array( 'ID' => $variation_id ) );
+ }
+ return $query;
+ }
+ );
+
+ while ( wc_update_1130_delete_unpublished_variation_lookup_rows() ) {
+ continue;
+ }
+
+ $this->assertTrue( $republished, 'The variation is published while the batch DELETE is issued.' );
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $remaining = array_map( 'intval', $wpdb->get_col( "SELECT product_id FROM {$lookup_table}" ) );
+ $this->assertSame( array( $variation_id ), $remaining, 'The rows of a variation published since its batch was selected are kept.' );
+ }
+
+ /**
+ * @testdox Migration handles unpublished variations 250 at a time and resumes after the last one it handled.
+ */
+ public function test_wc_update_1130_delete_unpublished_variation_lookup_rows_in_batches(): void {
+ global $wpdb;
+
+ include_once WC_ABSPATH . 'includes/wc-update-functions.php';
+
+ $lookup_table = $wpdb->prefix . 'wc_product_attributes_lookup';
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $wpdb->query( "DELETE FROM {$lookup_table}" );
+
+ $insert_post = function ( string $post_type, string $status, int $parent_id = 0 ) use ( $wpdb ): int {
+ $wpdb->insert(
+ $wpdb->posts,
+ array(
+ 'post_type' => $post_type,
+ 'post_status' => $status,
+ 'post_parent' => $parent_id,
+ ),
+ array( '%s', '%s', '%d' )
+ );
+ return (int) $wpdb->insert_id;
+ };
+
+ $parent_id = $insert_post( 'product', ProductStatus::PUBLISH );
+ $unpublished_ids = array();
+ $published_id = 0;
+ for ( $i = 0; $i < 251; $i++ ) {
+ // A published variation inside the first batch's id range must keep its rows.
+ if ( 125 === $i ) {
+ $published_id = $insert_post( 'product_variation', ProductStatus::PUBLISH, $parent_id );
+ }
+ $unpublished_ids[] = $insert_post( 'product_variation', ProductStatus::PRIVATE, $parent_id );
+ }
+
+ foreach ( array_merge( $unpublished_ids, array( $published_id ) ) as $variation_id ) {
+ $wpdb->insert(
+ $lookup_table,
+ array(
+ 'product_id' => $variation_id,
+ 'product_or_parent_id' => $parent_id,
+ 'taxonomy' => 'pa_size',
+ 'term_id' => 1,
+ 'is_variation_attribute' => 1,
+ 'in_stock' => 1,
+ ),
+ array( '%d', '%d', '%s', '%d', '%d', '%d' )
+ );
+ }
+
+ $this->assertSame(
+ '251',
+ $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->posts} WHERE post_type = 'product_variation' AND post_status != 'publish'" ),
+ 'Only the seeded variations are unpublished, so the batch boundaries are the ones asserted below.'
+ );
+
+ $remaining = function () use ( $wpdb, $lookup_table ): array {
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ return array_map( 'intval', $wpdb->get_col( "SELECT product_id FROM {$lookup_table} ORDER BY product_id" ) );
+ };
+
+ $this->assertTrue( wc_update_1130_delete_unpublished_variation_lookup_rows(), 'The first batch leaves work for another run.' );
+ $this->assertSame( array( $published_id, $unpublished_ids[250] ), $remaining(), 'The first batch handles the 250 lowest unpublished ids and skips the published one among them.' );
+
+ $this->assertTrue( wc_update_1130_delete_unpublished_variation_lookup_rows(), 'The second batch resumes after the last id of the first one.' );
+ $this->assertSame( array( $published_id ), $remaining(), 'The second batch handles the remaining unpublished variation.' );
+
+ $this->assertFalse( wc_update_1130_delete_unpublished_variation_lookup_rows(), 'An empty batch ends the migration.' );
+ $this->assertFalse( get_option( 'woocommerce_update_1130_last_unpublished_variation_id' ), 'The batch cursor is cleaned up once the migration is done.' );
+ }
+
+ /**
+ * @testdox Migration keeps going when another run has already saved the same or a later cursor.
+ *
+ * @testWith [0]
+ * [1000]
+ *
+ * @param int $ahead How far past this batch the other run has already moved the cursor.
+ */
+ public function test_wc_update_1130_delete_unpublished_variation_lookup_rows_accepts_a_concurrent_cursor( int $ahead ): void {
+ include_once WC_ABSPATH . 'includes/wc-update-functions.php';
+
+ $variation_id = $this->create_private_variation_with_lookup_row();
+ $option = 'woocommerce_update_1130_last_unpublished_variation_id';
+ $lookup_table = $GLOBALS['wpdb']->prefix . 'wc_product_attributes_lookup';
+ // The other run saves its cursor while this one is deleting the same batch. It is another process, so it writes the
+ // row without going through this process's option cache, which still holds the option as missing.
+ add_filter(
+ 'query',
+ function ( $query ) use ( $option, $variation_id, $ahead, $lookup_table ) {
+ global $wpdb;
+ if ( str_starts_with( ltrim( $query ), 'DELETE' ) && str_contains( $query, $lookup_table ) ) {
+ $wpdb->query(
+ $wpdb->prepare(
+ "INSERT INTO {$wpdb->options} ( option_name, option_value, autoload ) VALUES ( %s, %d, 'off' )",
+ $option,
+ $variation_id + $ahead
+ )
+ );
+ }
+ return $query;
+ }
+ );
+
+ $this->assertTrue( wc_update_1130_delete_unpublished_variation_lookup_rows(), 'A cursor saved by another run is progress, not a failure.' );
+ $this->assertGreaterThanOrEqual( $variation_id, (int) get_option( $option ), 'The saved cursor covers this batch.' );
+ }
+
+ /**
+ * @testdox Migration stops instead of reselecting the same batch when its cursor can't be saved.
+ */
+ public function test_wc_update_1130_delete_unpublished_variation_lookup_rows_stops_when_the_cursor_cannot_be_saved(): void {
+ global $wpdb;
+
+ include_once WC_ABSPATH . 'includes/wc-update-functions.php';
+
+ $this->create_private_variation_with_lookup_row();
+ $option = 'woocommerce_update_1130_last_unpublished_variation_id';
+ add_filter(
+ "pre_update_option_{$option}",
+ function ( $value, $old_value ) {
+ return $old_value;
+ },
+ 10,
+ 2
+ );
+ // Saving the variation queued its own invalidation, flush it so that only the migration's is observed.
+ WC_Cache_Helper::delete_transients_on_shutdown();
+ $counts_transient = 'wc_layered_nav_counts_pa_size';
+ set_transient( $counts_transient, array( 'query_hash' => array( 1 => 2 ) ) );
+
+ $this->assertFalse( wc_update_1130_delete_unpublished_variation_lookup_rows(), 'An unsaved cursor would select the same batch forever, so the migration stops.' );
+ $this->assertFalse( get_option( $option ), 'The cursor is cleaned up when the migration stops.' );
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $this->assertSame( '0', $wpdb->get_var( "SELECT COUNT(*) FROM {$wpdb->prefix}wc_product_attributes_lookup" ), 'The batch deleted before the stop stays deleted.' );
+ WC_Cache_Helper::delete_transients_on_shutdown();
+ $this->assertFalse( get_transient( $counts_transient ), 'The layered nav counts cached from the deleted batch are invalidated when the migration stops.' );
+ }
+
+ /**
+ * Create a variable product with one private variation, leaving that variation's row as the lookup table's only row.
+ *
+ * @return int The id of the private variation.
+ */
+ private function create_private_variation_with_lookup_row(): int {
+ global $wpdb;
+
+ $product = WC_Helper_Product::create_variation_product();
+ $variation_id = $product->get_children()[0];
+ $variation = wc_get_product( $variation_id );
+ $variation->set_status( ProductStatus::PRIVATE );
+ $variation->save();
+
+ $lookup_table = $wpdb->prefix . 'wc_product_attributes_lookup';
+ // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
+ $wpdb->query( "DELETE FROM {$lookup_table}" );
+ $wpdb->insert(
+ $lookup_table,
+ array(
+ 'product_id' => $variation_id,
+ 'product_or_parent_id' => $product->get_id(),
+ 'taxonomy' => 'pa_size',
+ 'term_id' => 1,
+ 'is_variation_attribute' => 1,
+ 'in_stock' => 1,
+ ),
+ array( '%d', '%d', '%s', '%d', '%d', '%d' )
+ );
+
+ return $variation_id;
+ }
+
/**
* @testdox wc_update_1120_cleanup_inherited_variation_images removes a variation thumbnail that duplicates the parent's featured image.
*/
diff --git a/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/FiltererTest.php b/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/FiltererTest.php
index 4403ffda408..bc60b0a3f16 100644
--- a/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/FiltererTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/FiltererTest.php
@@ -10,6 +10,7 @@ use Automattic\WooCommerce\Internal\ProductAttributesLookup\Filterer;
use Automattic\WooCommerce\RestApi\UnitTests\Helpers\ProductHelper;
use Automattic\WooCommerce\Utilities\ArrayUtil;
use Automattic\WooCommerce\Enums\ProductStockStatus;
+use Automattic\WooCommerce\Enums\ProductStatus;
/**
* Tests related to filtering for WC_Query.
@@ -526,6 +527,55 @@ class FiltererTest extends \WC_Unit_Test_Case {
$this->assert_counters( 'Color', array( 'Blue' ), 'or' );
}
+ /**
+ * Create a variable product with a single, in-stock, published Red variation.
+ *
+ * @return array Product and variation ids, as returned by create_variable_product.
+ */
+ private function create_variable_product_with_red_variation() {
+ return $this->create_variable_product(
+ array(
+ 'variation_attributes' => array( 'Color' => array( 'Red' ) ),
+ 'non_variation_attributes' => array(),
+ 'variations' => array(
+ array(
+ 'in_stock' => true,
+ 'defining_attributes' => array( 'Color' => 'Red' ),
+ ),
+ ),
+ )
+ );
+ }
+
+ /**
+ * @testdox Disabling a variation removes its product from lookup table filtering and its term from the widget counts.
+ */
+ public function test_lookup_filtering_excludes_disabled_variations() {
+ $this->set_use_lookup_table( true );
+ $this->create_product_attribute( 'Color', array( 'Red' ) );
+ $products = array(
+ $this->create_variable_product_with_red_variation(),
+ $this->create_variable_product_with_red_variation(),
+ );
+
+ $this->assertEqualsCanonicalizing(
+ array( $products[0]['id'], $products[1]['id'] ),
+ $this->do_product_request( array( 'Color' => array( 'Red' ) ) )
+ );
+ \WC_Query::reset_chosen_attributes();
+
+ $variation = wc_get_product( $products[0]['variation_ids'][0] );
+ $variation->set_status( ProductStatus::PRIVATE );
+ self::with_direct_product_attribute_lookup_updates(
+ function () use ( $variation ) {
+ $variation->save();
+ }
+ );
+
+ $this->assertSame( array( $products[1]['id'] ), $this->do_product_request( array( 'Color' => array( 'Red' ) ) ) );
+ $this->assert_counters( 'Color', array( 'Red' ), 'or' );
+ }
+
/**
* Assert that the filter by attribute widget lists a given set of terms for an attribute
* (with a count of 1 each)
diff --git a/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/LookupDataStoreTest.php b/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/LookupDataStoreTest.php
index 59ca751b7fa..b611a0d1b05 100644
--- a/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/LookupDataStoreTest.php
+++ b/plugins/woocommerce/tests/php/src/Internal/ProductAttributesLookup/LookupDataStoreTest.php
@@ -10,6 +10,7 @@ use Automattic\WooCommerce\RestApi\UnitTests\Helpers\ProductHelper;
use Automattic\WooCommerce\Internal\ProductAttributesLookup\LookupDataStore;
use Automattic\WooCommerce\Testing\Tools\FakeQueue;
use Automattic\WooCommerce\Enums\ProductStockStatus;
+use Automattic\WooCommerce\Enums\ProductStatus;
/**
* Tests for the LookupDataStore class.
@@ -1263,6 +1264,350 @@ class LookupDataStoreTest extends \WC_Unit_Test_Case {
$this->assertEquals( $expected, $actual );
}
+ /**
+ * @testdox 'on_product_changed' regenerates the data for a variation when its status changes and the "direct updates" option is on.
+ *
+ * @testWith [false]
+ * [true]
+ *
+ * @param bool $use_optimized_db_access 'true' to use optimized db access for the table update.
+ */
+ public function test_on_variation_status_changed_regenerates_data( bool $use_optimized_db_access ) {
+ if ( $use_optimized_db_access ) {
+ update_option( 'woocommerce_attribute_lookup_optimized_updates', 'yes' );
+ $this->sut = new LookupDataStore();
+ }
+ $this->set_direct_update_option( true );
+
+ list( $product, $variation ) = $this->create_variable_product_with_one_variation();
+ $this->empty_lookup_table();
+
+ $this->sut->on_product_changed( $variation, array( 'status' => ProductStatus::PUBLISH ) );
+
+ $this->assertEquals( array( $this->variation_lookup_row( $product, $variation ) ), $this->get_lookup_table_data() );
+ }
+
+ /**
+ * @testdox 'on_product_changed' ignores a status change of a product that is not a variation.
+ */
+ public function test_on_product_status_changed_does_nothing() {
+ $this->set_direct_update_option( true );
+
+ list( $product ) = $this->create_variable_product_with_one_variation();
+ $this->empty_lookup_table();
+
+ $this->sut->on_product_changed( $product, array( 'status' => ProductStatus::DRAFT ) );
+
+ $this->assertEmpty( $this->get_lookup_table_data() );
+ }
+
+ /**
+ * @testdox `create_data_for_product` creates no entries for variations that are not published.
+ *
+ * @testWith ["private", false]
+ * ["private", true]
+ * ["draft", false]
+ * ["draft", true]
+ * ["pending", false]
+ * ["pending", true]
+ *
+ * @param string $status The status of the variation that is not published.
+ * @param bool $use_optimized_db_access 'true' to use optimized db access for the table update.
+ */
+ public function test_create_data_for_variable_product_skips_unpublished_variations( string $status, bool $use_optimized_db_access ) {
+ list( $product, $published_variation ) = $this->create_variable_product_with_one_variation();
+
+ $unpublished_variation = new \WC_Product_Variation();
+ $unpublished_variation->set_attributes( array( self::$attributes[1]['name'] => 'term_2_2' ) );
+ $unpublished_variation->set_stock_status( ProductStockStatus::IN_STOCK );
+ $unpublished_variation->set_parent_id( $product->get_id() );
+ $unpublished_variation->set_status( $status );
+ $unpublished_variation->save();
+
+ $product->set_children( array( $published_variation->get_id(), $unpublished_variation->get_id() ) );
+ \WC_Product_Variable::sync( $product );
+ $this->empty_lookup_table();
+
+ $this->sut->create_data_for_product( $product, $use_optimized_db_access );
+
+ $this->assertEquals( array( $this->variation_lookup_row( $product, $published_variation ) ), $this->get_lookup_table_data() );
+ }
+
+ /**
+ * @testdox `create_data_for_product` creates no entries for a variation that is not published.
+ *
+ * @testWith ["private", false]
+ * ["private", true]
+ * ["draft", false]
+ * ["draft", true]
+ * ["pending", false]
+ * ["pending", true]
+ * ["trash", false]
+ * ["trash", true]
+ *
+ * @param string $status The status of the variation that is not published.
+ * @param bool $use_optimized_db_access 'true' to use optimized db access for the table update.
+ */
+ public function test_create_data_for_unpublished_variation_creates_nothing( string $status, bool $use_optimized_db_access ) {
+ list( $product, $variation ) = $this->create_variable_product_with_one_variation();
+ $variation->set_status( $status );
+ $variation->save();
+ $this->empty_lookup_table();
+ $this->insert_lookup_table_data( $variation->get_id(), $product->get_id(), self::$attributes[1]['name'], self::$attributes[1]['term_ids'][0], true, true );
+
+ $this->sut->create_data_for_product( $variation, $use_optimized_db_access );
+
+ $this->assertEmpty( $this->get_lookup_table_data(), 'Stale rows of an unpublished variation are removed and none are recreated.' );
+ }
+
+ /**
+ * @testdox Disabling a variation removes its entries and re-enabling it recreates them, when the "direct updates" option is on.
+ */
+ public function test_disabling_and_enabling_a_variation_updates_its_data() {
+ $this->set_direct_update_option( true );
+
+ list( $product, $variation ) = $this->create_variable_product_with_one_variation();
+ $expected_row = $this->variation_lookup_row( $product, $variation );
+ $this->assertEquals( array( $expected_row ), $this->get_lookup_table_data(), 'Saving a published variation creates its row.' );
+
+ $variation->set_status( ProductStatus::PRIVATE );
+ $variation->save();
+ $this->assertEmpty( $this->get_lookup_table_data(), 'Disabling the variation removes its row.' );
+
+ $variation->set_status( ProductStatus::PUBLISH );
+ $variation->save();
+ $this->assertEquals( array( $expected_row ), $this->get_lookup_table_data(), 'Re-enabling the variation recreates its row.' );
+ }
+
+ /**
+ * @testdox `create_data_for_product` creates all the entries for a scheduled or auto-draft variable product with published variations.
+ *
+ * @testWith ["future", false]
+ * ["future", true]
+ * ["auto-draft", false]
+ * ["auto-draft", true]
+ *
+ * @param string $status The status of the variable product.
+ * @param bool $use_optimized_db_access 'true' to use optimized db access for the table update.
+ */
+ public function test_create_data_for_unpublished_variable_product_creates_all_entries( string $status, bool $use_optimized_db_access ): void {
+ list( $product, $variation_ids ) = $this->create_variable_product_with_variations( $status, 2 );
+ $this->empty_lookup_table();
+
+ $this->sut->create_data_for_product( $product, $use_optimized_db_access );
+
+ $this->assertFalse( $this->sut->get_last_create_operation_failed(), 'The operation succeeds.' );
+ $this->assertEqualsCanonicalizing( $this->variable_product_lookup_rows( $product, $variation_ids ), $this->get_lookup_table_data() );
+ }
+
+ /**
+ * @testdox `create_data_for_product` with optimized db access creates all the entries of a scheduled variable product that need more than one INSERT batch.
+ */
+ public function test_create_data_for_scheduled_variable_product_with_many_variations_creates_all_entries(): void {
+ list( $product, $variation_ids ) = $this->create_variable_product_with_variations( ProductStatus::FUTURE, 1 );
+ // The optimized path reads only posts and postmeta, so these variations are seeded directly:
+ // 101 variation rows and the parent row take two 100-row batches.
+ $attribute_meta_key = 'attribute_' . self::$attributes[1]['name'];
+ for ( $i = 0; $i < 100; $i++ ) {
+ $variation_ids[] = wp_insert_post(
+ array(
+ 'post_type' => 'product_variation',
+ 'post_status' => ProductStatus::PUBLISH,
+ 'post_parent' => $product->get_id(),
+ 'meta_input' => array(
+ $attribute_meta_key => 'term_2_1',
+ '_stock_status' => ProductStockStatus::IN_STOCK,
+ ),
+ )
+ );
+ }
+ $this->empty_lookup_table();
+
+ $this->sut->create_data_for_product( $product->get_id(), true );
+
+ $this->assertFalse( $this->sut->get_last_create_operation_failed(), 'The operation succeeds.' );
+ $this->assertEqualsCanonicalizing( $this->variable_product_lookup_rows( $product, $variation_ids ), $this->get_lookup_table_data() );
+ }
+
+ /**
+ * @testdox The entries regenerated for a scheduled variable product are kept when the product is published.
+ *
+ * @testWith [false]
+ * [true]
+ *
+ * @param bool $use_optimized_db_access 'true' to use optimized db access for the table update.
+ */
+ public function test_entries_of_scheduled_variable_product_are_kept_when_it_is_published( bool $use_optimized_db_access ): void {
+ list( $product, $variation_ids ) = $this->create_variable_product_with_variations( ProductStatus::FUTURE, 2 );
+ $this->empty_lookup_table();
+ $expected_rows = $this->variable_product_lookup_rows( $product, $variation_ids );
+
+ if ( $use_optimized_db_access ) {
+ update_option( 'woocommerce_attribute_lookup_optimized_updates', 'yes' );
+ $this->sut = new LookupDataStore();
+ }
+ $this->set_direct_update_option( true );
+
+ $this->sut->on_product_changed( $product, array( 'attributes' => array() ) );
+ $this->assertEqualsCanonicalizing( $expected_rows, $this->get_lookup_table_data(), 'An attribute change regenerates the entries of the scheduled product.' );
+
+ wp_publish_post( $product->get_id() );
+
+ $this->assertSame( ProductStatus::PUBLISH, get_post_status( $product->get_id() ) );
+ $this->assertEqualsCanonicalizing( $expected_rows, $this->get_lookup_table_data(), 'Publishing the product keeps its entries.' );
+ }
+
+ /**
+ * @testdox `create_data_for_product` with optimized db access creates nothing, without failing, for a variable product in a status it doesn't cover.
+ */
+ public function test_create_data_for_variable_product_in_uncovered_status_creates_nothing(): void {
+ global $wpdb;
+
+ list( $product ) = $this->create_variable_product_with_variations( ProductStatus::PUBLISH, 2 );
+ // Trashing a product through WordPress trashes its variations too, so only the parent status is changed here.
+ $wpdb->update( $wpdb->posts, array( 'post_status' => ProductStatus::TRASH ), array( 'ID' => $product->get_id() ) );
+ clean_post_cache( $product->get_id() );
+ $this->empty_lookup_table();
+
+ $this->sut->create_data_for_product( $product->get_id(), true );
+
+ $this->assertFalse( $this->sut->get_last_create_operation_failed(), 'The product is not processed as a variation of itself.' );
+ $this->assertEmpty( $this->get_lookup_table_data() );
+ }
+
+ /**
+ * Create a variable product with a non-variation attribute (self::$attributes[0], first term), a variation
+ * attribute (self::$attributes[1], all three terms), and published, in-stock variations defined by 'term_2_1'.
+ *
+ * @param string $status The status of the variable product; a 'future' product is scheduled a week ahead.
+ * @param int $variations_count How many variations to create.
+ * @return array The product and the ids of its variations: [ \WC_Product_Variable, int[] ].
+ */
+ private function create_variable_product_with_variations( string $status, int $variations_count ): array {
+ $product = new \WC_Product_Variable();
+ $this->set_product_attributes(
+ $product,
+ array(
+ self::$attributes[0]['name'] => array(
+ 'id' => self::$attributes[0]['id'],
+ 'options' => array( self::$attributes[0]['term_ids'][0] ),
+ ),
+ self::$attributes[1]['name'] => array(
+ 'id' => self::$attributes[1]['id'],
+ 'options' => self::$attributes[1]['term_ids'],
+ 'variation' => true,
+ ),
+ )
+ );
+ $product->set_stock_status( ProductStockStatus::IN_STOCK );
+ $product->set_status( $status );
+ if ( ProductStatus::FUTURE === $status ) {
+ $product->set_date_created( time() + WEEK_IN_SECONDS );
+ }
+ $product->save();
+
+ $variation_ids = array();
+ for ( $i = 0; $i < $variations_count; $i++ ) {
+ $variation = new \WC_Product_Variation();
+ $variation->set_attributes( array( self::$attributes[1]['name'] => 'term_2_1' ) );
+ $variation->set_stock_status( ProductStockStatus::IN_STOCK );
+ $variation->set_parent_id( $product->get_id() );
+ $variation_ids[] = $variation->save();
+ }
+
+ $product->set_children( $variation_ids );
+ \WC_Product_Variable::sync( $product );
+
+ return array( $product, $variation_ids );
+ }
+
+ /**
+ * The lookup table rows expected for a product created by create_variable_product_with_variations.
+ *
+ * @param \WC_Product_Variable $product The parent product.
+ * @param int[] $variation_ids The ids of the variations.
+ * @return array Rows in the format returned by get_lookup_table_data.
+ */
+ private function variable_product_lookup_rows( \WC_Product_Variable $product, array $variation_ids ): array {
+ $rows = array(
+ array(
+ 'product_id' => $product->get_id(),
+ 'product_or_parent_id' => $product->get_id(),
+ 'taxonomy' => self::$attributes[0]['name'],
+ 'term_id' => self::$attributes[0]['term_ids'][0],
+ 'is_variation_attribute' => 0,
+ 'in_stock' => 1,
+ ),
+ );
+
+ foreach ( $variation_ids as $variation_id ) {
+ $rows[] = array(
+ 'product_id' => $variation_id,
+ 'product_or_parent_id' => $product->get_id(),
+ 'taxonomy' => self::$attributes[1]['name'],
+ 'term_id' => self::$attributes[1]['term_ids'][0],
+ 'is_variation_attribute' => 1,
+ 'in_stock' => 1,
+ );
+ }
+
+ return $rows;
+ }
+
+ /**
+ * Create a published variable product with one variation attribute (self::$attributes[1], all three terms)
+ * and one published, in-stock variation defined by the first term ('term_2_1').
+ *
+ * @return array The product and the variation: [ \WC_Product_Variable, \WC_Product_Variation ].
+ */
+ private function create_variable_product_with_one_variation(): array {
+ $variation_attribute = self::$attributes[1];
+
+ $product = new \WC_Product_Variable();
+ $this->set_product_attributes(
+ $product,
+ array(
+ $variation_attribute['name'] => array(
+ 'id' => $variation_attribute['id'],
+ 'options' => $variation_attribute['term_ids'],
+ 'variation' => true,
+ ),
+ )
+ );
+ $product->set_stock_status( ProductStockStatus::IN_STOCK );
+ $product->save();
+
+ $variation = new \WC_Product_Variation();
+ $variation->set_attributes( array( $variation_attribute['name'] => 'term_2_1' ) );
+ $variation->set_stock_status( ProductStockStatus::IN_STOCK );
+ $variation->set_parent_id( $product->get_id() );
+ $variation->save();
+
+ $product->set_children( array( $variation->get_id() ) );
+ \WC_Product_Variable::sync( $product );
+
+ return array( $product, $variation );
+ }
+
+ /**
+ * The lookup table row expected for a published, in-stock variation created by
+ * create_variable_product_with_one_variation.
+ *
+ * @param \WC_Product_Variable $product The parent product.
+ * @param \WC_Product_Variation $variation The variation.
+ * @return array Row in the format returned by get_lookup_table_data.
+ */
+ private function variation_lookup_row( \WC_Product_Variable $product, \WC_Product_Variation $variation ): array {
+ return array(
+ 'product_id' => $variation->get_id(),
+ 'product_or_parent_id' => $product->get_id(),
+ 'taxonomy' => self::$attributes[1]['name'],
+ 'term_id' => self::$attributes[1]['term_ids'][0],
+ 'is_variation_attribute' => 1,
+ 'in_stock' => 1,
+ );
+ }
+
/**
* Set the product attributes from an array with this format:
*