Tags and Categories are not related to each other. They both tied to posts, so that we need to query for all the posts/products assigned to the top level category.
First, We need to recursively query for ID of categories that points to our initial category and every category that points to its child. To select all categories and its subcategories, we could use recursive CTA (as of MySQL 8.0, MariaDB 10.2 and UP).
Finally, we need to query all the tags that are related to the above posts. We do that with use of
Now, we need to combine all of the above in to a single query. The solution looks like this:
-- define (parent/starting) category SET @parentCategoryId := 17; -- define recursive function here WITH recursive cteCategory(term_id) AS ( SELECT t.term_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' -- <<< category UNION ALL SELECT t2.term_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_id ) -- start query for tags used in collection of post/products SELECT DISTINCT t.* FROM wp_posts AS p LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN wp_terms t ON t.term_id = tt.term_id WHERE p.post_type="product" -- <<< post AND p.post_status = 'publish' AND tt.taxonomy = "product_tag" -- <<< post_tag AND p.ID IN -- build list of posts/products listed under initial category and their childrens (SELECT p.ID FROM wp_posts AS p LEFT JOIN wp_term_relationships tr ON p.ID = tr.object_id LEFT JOIN wp_term_taxonomy tt ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN wp_terms t ON t.term_id = tt.term_id WHERE p.post_type="product" -- <<< post AND p.post_status = 'publish' AND tt.taxonomy = "product_cat" -- <<< category AND tt.term_taxonomy_id IN (SELECT * FROM cteCategory) -- call recursive function cteCategory -- AND FIND_IN_SET(t.name, @CategoryByName) -- <<< search category by name ORDER BY p.ID) ORDER BY p.ID;
The above query will return only not empty tags. It is because we build our tag list based on product IDs in certain category group. That said all tags are extracted from products therefore they are all not empty. If you need to query all tags regardless from are they are empty or not, see this post: https://devwl.pl/woocommerce-sql-query-for-not-empty-tags/