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 IN directive.

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/

0
Would love your thoughts, please comment.x
()
x