Got it, I’ve got a clean solution for you using MySQL’s Recursive Common Table Expressions (CTE). This lets you pull the complete category hierarchy (from root to child) in one query, no matter how deep your category tree is—no more step-by-step lookups!

The Recursive CTE Query #

This query will return the full breadcrumb path for a specific product (replace 2051 with your product's object_id):

WITH RECURSIVE category_hierarchy AS (
    -- Step 1: Anchor member - Get the product's direct category(ies)
    SELECT
        tt.term_id,
        t.name,
        tt.parent,
        1 AS depth
    FROM wp_term_relationships tr
    INNER JOIN wp_term_taxonomy tt 
        ON tr.term_taxonomy_id = tt.term_taxonomy_id
    INNER JOIN wp_terms t 
        ON tt.term_id = t.term_id
    WHERE tr.object_id = '2051' -- Your product's object_id here
      AND tt.taxonomy = 'product_cat'

    UNION ALL

    -- Step 2: Recursive member - Traverse up to the root category
    SELECT
        tt.term_id,
        t.name,
        tt.parent,
        ch.depth + 1 AS depth
    FROM category_hierarchy ch
    INNER JOIN wp_term_taxonomy tt 
        ON ch.parent = tt.term_id
    INNER JOIN wp_terms t 
        ON tt.term_id = t.term_id
    WHERE tt.taxonomy = 'product_cat'
      AND tt.parent != 0 -- Stop when we hit the root (parent = 0)
)
-- Combine categories into a breadcrumb string, ordered root-to-child
SELECT GROUP_CONCAT(name SEPARATOR ' > ') AS category_breadcrumb
FROM category_hierarchy
ORDER BY depth DESC; -- Reverse depth to put root first

How It Works #

1. Anchor Member: This part grabs the direct product category(ies) linked to your product by joining wp_term_relationships, wp_term_taxonomy, and wp_terms. It sets an initial depth of 1 for the childmost category.
2. Recursive Member: This part repeatedly joins the CTE back to itself to fetch each parent category, incrementing the depth each time. It stops when it reaches the root category (where parent = 0).
3. Final Select: Uses GROUP_CONCAT to stitch the category names into a single breadcrumb string, ordered from root to child by reversing the depth order.

Handling Multiple Categories #

If your product is assigned to multiple categories, this query will return a breadcrumb for each category. If you only want the main product category, modify the anchor member to target the _product_cat post meta (WooCommerce stores the main category here):

-- Anchor member for main category only
SELECT
    tt.term_id,
    t.name,
    tt.parent,
    1 AS depth
FROM wp_postmeta pm
INNER JOIN wp_term_taxonomy tt 
    ON pm.meta_value = tt.term_id
INNER JOIN wp_terms t 
    ON tt.term_id = t.term_id
WHERE pm.post_id = '2051' -- Your product's object_id
  AND pm.meta_key = '_product_cat'
  AND tt.taxonomy = 'product_cat'

Example Output #

For your iPhone case example, the query will return:

Products > Mobile > Mobile Accessories > Cases

内容的提问来源于stack exchange，提问作者rainbowpol