As the nesting level of the categories is limited, an approach with joins can be taken. Otherwise, a CTE would be a better approach.
My suggestion is based on the product category association. For each category, the parent is joined and the parent of the parent. The select gets the name of the category depending on the nesting level of the association.
SELECT
CASE
-- association level 1 category
WHEN parent.entity_id IS NULL THEN category.name
-- association level 2 category
WHEN grand_parent.entity_id IS NULL THEN parent.name
-- association level 3 category
ELSE grand_parent.name
END AS level_1,
CASE
-- association level 1 category
WHEN parent.entity_id IS NULL THEN NULL
-- association level 2 category
when grand_parent.entity_id IS NULL THEN category.name
-- association level 3 category
ELSE parent.name
END AS level_2,
CASE
-- association level 3 category
WHEN grand_parent.entity_id IS NOT NULL THEN category.name
-- association level 2 or 1 category
ELSE NULL
END AS level_3,
product.*
FROM catalog_category_product AS ccp
JOIN magento_dev.catalog_product_flat_1 AS product
ON ccp.product_id = product.entity_id
JOIN catalog_category_flat_store_1 AS category
ON ccp.category_id = category.entity_id
AND category.is_active = 1
LEFT JOIN catalog_category_flat_store_1 AS parent
ON category.parent_id = parent.entity_id
AND parent.level >= 2
LEFT JOIN catalog_category_flat_store_1 AS grand_parent
ON parent.parent_id = grand_parent.entity_id
AND grand_parent.level >= 2
As you see, the flat tables are used (to access the category name directly and not with another join), which requires up to date indexes. During my tests, the table catalog_category_product
was also updated only through reindexing.
I'm not sure if this is what you expected/requested, but I hope it gives you a hint.