
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?