WooCommerce – get all children of a parent product category

WooCommerce SQL – List children category of specific category id

If you need to list all categories listed under a specific parent category, you can run recursive SQL query. See the example below:

SET @parentCategoryId := 17; -- <<< parent post ID here

WITH recursive cteCategory(term_id, parent, name, taxonomy, slug) AS
(   
    SELECT t.term_id, tt.parent, t.name, tt.taxonomy, t.slug FROM wp_terms t
    LEFT JOIN wp_term_taxonomy tt 
    ON tt.term_taxonomy_id = t.term_id
    WHERE tt.term_taxonomy_id = @parentCategoryId 
    AND tt.taxonomy = 'product_cat' -- <<< use "category" for WP post category
    UNION ALL
    SELECT t2.term_id, tt2.parent, t2.name, tt2.taxonomy, t2.slug FROM wp_terms t2
    LEFT JOIN wp_term_taxonomy tt2 
    ON tt2.term_taxonomy_id = t2.term_id
    INNER JOIN cteCategory
    ON tt2.parent = cteCategory.term_id
)


SELECT * FROM cteCategory; -- include parent category id
-- SELECT * FROM cteCategory cte WHERE cte.term_id != @parentCategoryId; -- only childrens

-- This would also work:
SET @parentCategoryId := 17;

WITH recursive cteCategory(term_taxonomy_id) AS
(   
    SELECT tt.term_taxonomy_id FROM wp_terms t
    LEFT JOIN wp_term_taxonomy tt 
    ON tt.term_taxonomy_id = t.term_id
    WHERE tt.term_taxonomy_id = @parentCategoryId 
    AND tt.taxonomy = 'product_cat'
    UNION ALL
    SELECT tt2.term_taxonomy_id FROM wp_terms t2
    LEFT JOIN wp_term_taxonomy tt2 
    ON tt2.term_taxonomy_id = t2.term_id
    INNER JOIN cteCategory
    ON tt2.parent = cteCategory.term_taxonomy_id
)

SELECT * FROM cteCategory;

Output:

+---------+--------+---------------------+-------------+---------------------+
| term_id | parent | name                | taxonomy    | slug                |
+---------+--------+---------------------+-------------+---------------------+
|      17 |      0 | Warzywa             | product_cat | warzywa             |
|      56 |     17 | pomidory            | product_cat | pomidory            |
|      57 |     56 | pomidory koktailowe | product_cat | pomidory-koktailowe |
+---------+--------+---------------------+-------------+---------------------+
3 rows in set (0.001 sec)

Select top level categories

-- Query all categories those empty and those with parent category class
SELECT t.term_id, t.name, t.slug,  tt.taxonomy, tt.parent, tt.count FROM wp_terms t
LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_taxonomy_id
WHERE tt.taxonomy = "product_cat" -- <<<
AND tt.parent = 0 -- only top level categories
-- AND tt.count = > 0 -- not empty
ORDER BY tt.taxonomy;

Output:

+---------+-----------+-----------+-------------+--------+-------+
| term_id | name      | slug      | taxonomy    | parent | count |
+---------+-----------+-----------+-------------+--------+-------+
|      16 | Nowe      | nowe      | product_cat |      0 |     0 |
|      17 | Warzywa   | warzywa   | product_cat |      0 |     4 |
|      18 | Owoce     | owoce     | product_cat |      0 |     3 |
|      19 | Soki      | soki      | product_cat |      0 |     0 |
|      20 | Przetwory | przetwory | product_cat |      0 |     0 |
+---------+-----------+-----------+-------------+--------+-------+
5 rows in set (0.001 sec)

You can reed more about recurion here: https://mariadb.com/kb/en/recursive-common-table-expressions-overview/ and here How to create a MySQL hierarchical recursive query?