allanninal.dev / woo

Field notes / WooCommerce core: products and catalog

Repair WooCommerce core: products and catalog

Orphaned product variations: cleaning up variations with no parent product

A product gets deleted, trashed, or switched from a variable product back to a simple one, and somewhere in the database its child variations are still sitting there, each one a real post of its own with nothing left to load it under. Nobody sees them in the catalog, but they still show up in stock reports, background jobs, and old order line items. Here is why WooCommerce leaves them behind and a small script that finds every orphan and cleans it up safely.

Python and Node.js Runs on a schedule or ad hoc Safe by default (dry run)
A pile of baby bottles sitting next to each other
Photo by Alexander Van Steenberge on Unsplash
The short answer

A variation is stored as its own product_variation post, separate from its parent. When the parent product is deleted, trashed, or its type is changed away from variable, WooCommerce does not reliably clean up the child variations first, so they become orphans. Run a small Python or Node.js script that checks each known variation id against the WooCommerce REST API, confirms whether its parent still exists and is still variable, and moves the true orphans to trash. Full code, tests, and a dry run guard are below.

The problem in plain words

Every variation in a variable product, a shirt in size medium and color blue for example, is not just a row of attributes tucked inside the parent. WooCommerce stores it as its own post of type product_variation, with its own id, its own price, its own stock, and a parent_id pointing back to the product it belongs to.

That design is normally invisible. But it means deleting the parent product does not automatically delete the children in every code path. A parent removed through a plugin, an import tool, a direct database change, or a bulk action that skips the usual WordPress delete hooks can vanish while its variations stay behind. The variations are still real posts, they still have stock numbers, and they still have entries in the product lookup table, but there is no parent left that any storefront page, admin screen, or REST API call can attach them to.

Variable product parent id 100 Variation id 501 Variation id 502 ✕ deleted, trashed, or changed to simple Parent gone nothing at id 100 Orphaned variations, parent id 100 points nowhere Stale stock rows and errors
The parent is gone, but the variation posts are still in the database, still holding stock, still pointing at a parent id that no longer resolves to anything.

Why it happens

WooCommerce ties variations to their parent with a plain post_parent relationship in WordPress, and it relies on that relationship being torn down cleanly whenever a parent goes away. A few paths skip that cleanup:

Because the leftover posts are still valid product_variation entries, WooCommerce's product lookup table and stock sync jobs can still pick them up, which is usually how a store first notices something is wrong, an inventory report with more rows than products, or a sync job that logs an error trying to read a parent that returns nothing.

The key insight

A variation without a resolvable parent is not a product anyone can buy, but it is still real data taking up space and still capable of confusing anything that walks the catalog. The fix is not to guess. It is to check, for each variation you suspect is orphaned, whether its parent record can still be loaded and is still a variable product, and only act on the ones where that check clearly fails.

The fix, as a flow

We do not touch the live catalog blindly. We start from a list of variation ids worth checking, gathered from wherever a store first notices the mismatch, a stock export, an old order line item, or a lookup table dump, then look up each variation's parent through the WooCommerce REST API. If the parent cannot be found, is trashed, or is no longer type variable, we treat the variation as an orphan and move it to trash, never a permanent delete, so it can be restored if the call was wrong.

Candidate variation ids from an export or order data Look up variation and its parent_id Load parent product by parent_id Exists and still variable? yes Leave it alone no, orphan Move to trash not a permanent delete
The script only trashes a variation once its parent has clearly failed the check. Anything with a healthy, still variable parent is left untouched.

Build it step by step

1

Get access and gather candidate ids

You need a WooCommerce REST API key pair (a consumer key and a consumer secret) with read and write access to products, created under WooCommerce, Settings, Advanced, REST API. You also need a starting list of variation ids to check, pulled from a stock export, an old order's line items, or a dump of the product lookup table. Keep every credential in environment variables, never in the file.

setup (shell)
pip install requests

export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export CANDIDATE_VARIATION_IDS="501,502,733"
export DRY_RUN="true"   # start safe, change to false to write
setup (shell)
npm install

export WOO_STORE_URL="https://yourstore.com"
export WOO_CONSUMER_KEY="ck_..."
export WOO_CONSUMER_SECRET="cs_..."
export CANDIDATE_VARIATION_IDS="501,502,733"
export DRY_RUN="true"   // start safe, change to false to write
2

Look up each variation and its parent id

The WooCommerce REST API has no dedicated endpoint for listing all variations across the store, only per parent. So for a variation id you already suspect, ask the generic products endpoint for it directly. If it resolves, the response includes the parent_id WooCommerce still has on file for it.

step2.py
import os, requests
from requests.auth import HTTPBasicAuth

WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])

def get_record_for(record_id):
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/products/{record_id}", auth=AUTH, timeout=30)
    if r.status_code == 404:
        return None
    r.raise_for_status()
    return r.json()
step2.js
const WOO_URL = process.env.WOO_STORE_URL.replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
  `${process.env.WOO_CONSUMER_KEY}:${process.env.WOO_CONSUMER_SECRET}`
).toString("base64");

async function woo(path, options = {}) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
    ...options,
    headers: { "Content-Type": "application/json", Authorization: AUTH, ...(options.headers || {}) },
  });
  if (res.status === 404) return null;
  if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
  return res.json();
}
3

Load the parent and confirm it is still variable

Take the parent_id from the variation record and ask the same endpoint for the parent. A missing response means the parent is gone. A response with status: "trash" means it was trashed but not yet emptied. A response with type no longer equal to variable means it was converted to a simple product and the old variations were left stranded.

4

Decide, with one pure function

Keep the decision in its own function that takes a variation record and its parent record and returns an action. Because it is pure, no network calls inside it, it is easy to read and easy to test, which we do later. The rule is simple. If the variation has no parent id at all, skip it, it is not actually a variation. If the parent cannot be found, is trashed, or is not variable, it is an orphan. Otherwise it is fine as is.

decide.py
def decide(variation, parent):
    if variation is None:
        return ("skip", "variation itself no longer exists")
    parent_id = variation.get("parent_id")
    if not parent_id:
        return ("skip", "not a variation, no parent_id set")
    if parent is None:
        return ("orphan", "parent product no longer exists")
    if parent.get("status") == "trash":
        return ("orphan", "parent product is trashed")
    if parent.get("type") != "variable":
        return ("orphan", "parent product is no longer a variable product")
    return ("ok", "parent exists and is still variable")
decide.js
export function decide(variation, parent) {
  if (!variation) return ["skip", "variation itself no longer exists"];
  const parentId = variation.parent_id;
  if (!parentId) return ["skip", "not a variation, no parent_id set"];
  if (!parent) return ["orphan", "parent product no longer exists"];
  if (parent.status === "trash") return ["orphan", "parent product is trashed"];
  if (parent.type !== "variable") return ["orphan", "parent product is no longer a variable product"];
  return ["ok", "parent exists and is still variable"];
}
5

Trash the orphan, never a permanent delete

When the action is orphan, move the variation to trash with force=false. WooCommerce still treats the variation id as a regular product record for this call, so the same delete endpoint works. Trashing keeps the option to restore it open, which matters if the candidate list ever includes a false positive from stale data.

apply.py
def trash_variation(variation_id):
    requests.delete(
        f"{WOO_URL}/wp-json/wc/v3/products/{variation_id}",
        params={"force": "false"},
        auth=AUTH, timeout=30,
    ).raise_for_status()
apply.js
async function trashVariation(variationId) {
  await woo(`/products/${variationId}?force=false`, { method: "DELETE" });
}
6

Wire it together with a dry run guard

The loop ties every piece together. Notice the dry run guard. On the first run, leave DRY_RUN on so the script only reports what it would trash. Read the output, confirm each parent id genuinely is missing or no longer variable, then switch it off to let it write. This is the kind of job you run once after a cleanup, or on a slow schedule to catch new orphans early.

Run it safe

Always start with DRY_RUN=true. Trashing is reversible, but a candidate list built from stale data can still point at a variation that is perfectly healthy. Review the plan before it acts.

The full code

Here is the complete script in one file for each language. It reads settings from the environment, logs what it finds, respects the dry run flag, and is safe to run again and again because it never touches a variation whose parent still resolves and is still variable.

View this code on GitHub Full runnable folder with tests in the woocommerce-fixes repo.

find_orphaned_variations.py
"""Find (and optionally trash) WooCommerce product variations whose parent product
is gone or is no longer a variable product.

A variation is a real "product_variation" post of its own. When its parent product
is deleted, trashed, or its type is changed from variable to simple, WooCommerce does
not always clean up the child variations first. The orphan keeps its own row in
wp_posts and its own entry in the product lookup table, so it can still surface in
search, in stock reports, or on old cart and order line items, even though there is
no parent to load it under.

This walks a list of known variation ids (for example gathered from order line
items, a stock export, or the wp_postmeta table) and checks each one's parent
through the WooCommerce REST API. Read only by default. Run on a schedule or ad
hoc after a product cleanup.
"""
import os
import logging
import requests
from requests.auth import HTTPBasicAuth

logging.basicConfig(level=logging.INFO, format="%(asctime)s %(levelname)s %(message)s")
log = logging.getLogger("find_orphaned_variations")

WOO_URL = os.environ["WOO_STORE_URL"].rstrip("/")
AUTH = HTTPBasicAuth(os.environ["WOO_CONSUMER_KEY"], os.environ["WOO_CONSUMER_SECRET"])
DRY_RUN = os.environ.get("DRY_RUN", "true").lower() == "true"

# Variation ids to check. In practice this list comes from somewhere that still
# remembers old variation ids: an export, a lookup table dump, or order line items.
CANDIDATE_IDS_ENV = os.environ.get("CANDIDATE_VARIATION_IDS", "")


def candidate_ids():
    return [int(v) for v in CANDIDATE_IDS_ENV.split(",") if v.strip()]


def get_parent_of(variation_id):
    """Look up the parent_id WooCommerce has stored for a variation.

    The REST API has no top level /products/variations endpoint, so we ask the
    core /products/ endpoint. A variation's own id resolves through the normal
    posts table, and WooCommerce returns parent_id on any product-type response
    that has one, so this call also works when the id belongs to a variation.
    """
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/products/{variation_id}", auth=AUTH, timeout=30)
    if r.status_code == 404:
        return None
    r.raise_for_status()
    return r.json()


def get_parent_product(parent_id):
    r = requests.get(f"{WOO_URL}/wp-json/wc/v3/products/{parent_id}", auth=AUTH, timeout=30)
    if r.status_code == 404:
        return None
    r.raise_for_status()
    return r.json()


def decide(variation, parent):
    """Pure decision: given a variation record and its claimed parent record
    (or None if the lookup failed), decide what to do.

    variation: a dict with at least "id" and "parent_id".
    parent: the parent product dict, or None if it no longer exists.
    """
    if variation is None:
        return ("skip", "variation itself no longer exists")
    parent_id = variation.get("parent_id")
    if not parent_id:
        return ("skip", "not a variation, no parent_id set")
    if parent is None:
        return ("orphan", "parent product no longer exists")
    if parent.get("status") == "trash":
        return ("orphan", "parent product is trashed")
    if parent.get("type") != "variable":
        return ("orphan", "parent product is no longer a variable product")
    return ("ok", "parent exists and is still variable")


def trash_variation(parent_id_hint, variation_id):
    """Move the orphaned variation to trash. We use the variation's own id against
    the generic products endpoint with force=false, which moves it to trash rather
    than deleting permanently, so it can still be restored if this was a mistake.
    """
    requests.delete(
        f"{WOO_URL}/wp-json/wc/v3/products/{variation_id}",
        params={"force": "false"},
        auth=AUTH, timeout=30,
    ).raise_for_status()


def run():
    orphaned = 0
    for variation_id in candidate_ids():
        variation = get_parent_of(variation_id)
        parent = get_parent_product(variation["parent_id"]) if variation and variation.get("parent_id") else None
        action, reason = decide(variation, parent)
        if action != "orphan":
            continue
        log.warning("Variation %s: %s. %s", variation_id, reason, "would trash" if DRY_RUN else "trashing")
        if not DRY_RUN:
            trash_variation(variation.get("parent_id"), variation_id)
        orphaned += 1
    log.info("Done. %d orphaned variation(s) %s.", orphaned, "found" if DRY_RUN else "trashed")


if __name__ == "__main__":
    run()
find-orphaned-variations.js
/**
 * Find (and optionally trash) WooCommerce product variations whose parent product
 * is gone or is no longer a variable product.
 *
 * A variation is a real "product_variation" post of its own. When its parent product
 * is deleted, trashed, or its type is changed from variable to simple, WooCommerce
 * does not always clean up the child variations first. The orphan keeps its own row
 * in wp_posts and its own entry in the product lookup table, so it can still surface
 * in search, in stock reports, or on old cart and order line items, even though there
 * is no parent to load it under.
 *
 * This walks a list of known variation ids (for example gathered from order line
 * items, a stock export, or the wp_postmeta table) and checks each one's parent
 * through the WooCommerce REST API. Read only by default. Run on a schedule or ad
 * hoc after a product cleanup.
 *
 * Guide: https://www.allanninal.dev/woocommerce/orphaned-product-variations/
 */
import { pathToFileURL } from "node:url";

const WOO_URL = (process.env.WOO_STORE_URL || "https://example.com").replace(/\/$/, "");
const AUTH = "Basic " + Buffer.from(
  `${process.env.WOO_CONSUMER_KEY || "ck_dummy"}:${process.env.WOO_CONSUMER_SECRET || "cs_dummy"}`
).toString("base64");
const DRY_RUN = (process.env.DRY_RUN || "true").toLowerCase() === "true";

// Variation ids to check. In practice this list comes from somewhere that still
// remembers old variation ids: an export, a lookup table dump, or order line items.
const CANDIDATE_IDS_ENV = process.env.CANDIDATE_VARIATION_IDS || "";

export function candidateIds() {
  return CANDIDATE_IDS_ENV.split(",").map((v) => v.trim()).filter(Boolean).map(Number);
}

async function woo(path, options = {}) {
  const res = await fetch(`${WOO_URL}/wp-json/wc/v3${path}`, {
    ...options,
    headers: { "Content-Type": "application/json", Authorization: AUTH, ...(options.headers || {}) },
  });
  if (res.status === 404) return null;
  if (!res.ok) throw new Error(`Woo ${path} returned ${res.status}`);
  return res.json();
}

export async function getRecordFor(id) {
  return woo(`/products/${id}`);
}

/**
 * Pure decision: given a variation record and its claimed parent record (or null
 * if the lookup failed), decide what to do.
 *
 * variation: an object with at least "id" and "parent_id".
 * parent: the parent product object, or null if it no longer exists.
 */
export function decide(variation, parent) {
  if (!variation) return ["skip", "variation itself no longer exists"];
  const parentId = variation.parent_id;
  if (!parentId) return ["skip", "not a variation, no parent_id set"];
  if (!parent) return ["orphan", "parent product no longer exists"];
  if (parent.status === "trash") return ["orphan", "parent product is trashed"];
  if (parent.type !== "variable") return ["orphan", "parent product is no longer a variable product"];
  return ["ok", "parent exists and is still variable"];
}

async function trashVariation(variationId) {
  await woo(`/products/${variationId}?force=false`, { method: "DELETE" });
}

export async function run() {
  let orphaned = 0;
  for (const variationId of candidateIds()) {
    const variation = await getRecordFor(variationId);
    const parent = variation && variation.parent_id ? await getRecordFor(variation.parent_id) : null;
    const [action, reason] = decide(variation, parent);
    if (action !== "orphan") continue;
    console.warn(`Variation ${variationId}: ${reason}. ${DRY_RUN ? "would trash" : "trashing"}`);
    if (!DRY_RUN) await trashVariation(variationId);
    orphaned++;
  }
  console.log(`Done. ${orphaned} orphaned variation(s) ${DRY_RUN ? "found" : "trashed"}.`);
}

if (import.meta.url === pathToFileURL(process.argv[1]).href) {
  run().catch((e) => { console.error(e); process.exit(1); });
}

Add a test

The decision rule is the part most worth testing, because it decides which variations get trashed. Because we kept decide pure, the test needs no network and no WooCommerce store. It just feeds in plain objects and checks the action.

test_orphaned_decide.py
from find_orphaned_variations import decide


def variation(**over):
    base = {"id": 501, "parent_id": 100}
    base.update(over)
    return base


def parent(**over):
    base = {"id": 100, "type": "variable", "status": "publish"}
    base.update(over)
    return base


def test_ok_when_parent_exists_and_variable():
    assert decide(variation(), parent())[0] == "ok"


def test_orphan_when_parent_missing():
    assert decide(variation(), None)[0] == "orphan"


def test_orphan_when_parent_trashed():
    assert decide(variation(), parent(status="trash"))[0] == "orphan"


def test_orphan_when_parent_converted_to_simple():
    assert decide(variation(), parent(type="simple"))[0] == "orphan"


def test_skip_when_variation_itself_gone():
    assert decide(None, None)[0] == "skip"


def test_skip_when_no_parent_id_set():
    assert decide(variation(parent_id=None), parent())[0] == "skip"
find-orphaned-variations.test.js
import { test } from "node:test";
import assert from "node:assert/strict";
import { decide } from "./find-orphaned-variations.js";

const variation = (over = {}) => ({ id: 501, parent_id: 100, ...over });
const parent = (over = {}) => ({ id: 100, type: "variable", status: "publish", ...over });

test("ok when parent exists and variable", () => {
  assert.equal(decide(variation(), parent())[0], "ok");
});

test("orphan when parent missing", () => {
  assert.equal(decide(variation(), null)[0], "orphan");
});

test("orphan when parent trashed", () => {
  assert.equal(decide(variation(), parent({ status: "trash" }))[0], "orphan");
});

test("orphan when parent converted to simple", () => {
  assert.equal(decide(variation(), parent({ type: "simple" }))[0], "orphan");
});

test("skip when variation itself gone", () => {
  assert.equal(decide(null, null)[0], "skip");
});

test("skip when no parent_id set", () => {
  assert.equal(decide(variation({ parent_id: null }), parent())[0], "skip");
});

Case studies

Database restore

The migration that only moved half the tree

A store moved hosts and a partial database restore brought over the posts table but missed a batch of postmeta updates. A handful of variable products came back as simple products, while their old variation posts stayed in the database with parent ids pointing at products that were now the wrong type.

The script ran in dry run against a list of variation ids pulled from a pre-migration export, found nineteen orphans in the type mismatch case, and trashed them once the team confirmed none were live products anyone still needed.

Bulk cleanup plugin

The bulk delete that skipped the children

A catalog cleanup plugin removed forty seasonal products in one pass to free up space before a new collection launch. The plugin's bulk delete call did not cascade to child variations, so roughly one hundred and sixty variation posts were left behind, each still holding a stock count.

A stock report started showing more rows than active products, which is what tipped the team off. The script confirmed every flagged variation's parent id was gone, and trashing them brought the stock report back in line with the real catalog.

What good looks like

After a cleanup pass, stock reports and lookup tables match the products a shopper can actually see, and background jobs stop tripping over parent ids that resolve to nothing. Keep the candidate list handy and re-run the check after any bulk product change, since a rushed cleanup is exactly when new orphans tend to appear.

FAQ

What is an orphaned product variation in WooCommerce?

It is a product_variation post that still exists in the database after its parent product was deleted, trashed, or changed from a variable product to a simple one. WooCommerce does not always clean up the child variations when that happens, so the orphan is left with nothing to load it under.

How do orphaned variations cause problems if nobody can see them?

They still have their own row in the posts table and their own entry in the product lookup table, so they can appear in stock reports, background sync jobs, or old cart and order line items, and they can throw errors when something tries to load a parent that is no longer there.

Is it safe to delete orphaned variations with a script?

Yes, when the script confirms the parent product record cannot be found, is trashed, or is no longer a variable product before acting, and it moves variations to trash rather than deleting them permanently. Start in dry run mode to review the list before it writes.

Related field notes

Orders stuck on Pending after a successful Stripe payment Order marked paid with no charge

Citations

On the problem:

  1. WooCommerce developer docs: product variations are stored as their own post type, product_variation, linked to a parent by post_parent. developer.woocommerce.com/docs/category/products
  2. WordPress developer reference: wp_delete_post and how child post cleanup depends on the delete path used. developer.wordpress.org/reference/functions/wp_delete_post
  3. WooCommerce community discussion on stray product_variation rows appearing after bulk product deletes. wordpress.org/support/plugin/woocommerce

On the solution:

  1. WooCommerce REST API: retrieve, update, and delete a product or variation, including the force parameter for trash versus permanent delete. woocommerce.github.io/woocommerce-rest-api-docs
  2. WooCommerce docs: managing product variations and how they relate to their parent product. woocommerce.com/document/managing-product-variations
  3. WooCommerce docs: the product lookup table and how it is kept in sync with product and variation data. woocommerce.com/document

Stuck on a tricky one?

If you have a bug in WooCommerce, WooCommerce Subscriptions, or the WooCommerce Stripe gateway that you would rather hand off, this is the kind of work I do. Message me and we can work through it together.

Contact me on LinkedIn

Did this clean up your catalog?

If this helped you make sense of a messy stock report or a stray variation error, you can buy me a coffee. It is the best way to keep these field notes free and growing.

Buy me a coffee on Ko-fi

Back to all WooCommerce field notes