Query WordPress Categories and Tags in a nutshell

SQL, WooCommerce, WordPress | 0 comments

WordPress Categories

They meant to group its content (posts / custom posts / products) in more general groups such as “PC COMPONENTS”, “TV” or “POLITICS”, “FASHION”, “NEWS”, “FINANCE” and so on. A category can relate to a parent category, creating subcategories / hierarchical structure. While categories and tags are stored in the same tables, from a WordPress application GUI there is no way to create hierarchical structure for tags.

Category structure example:

  1. PC COMPONENTS
    1. GRAPHIC CARDS
    2. PROCESSORS
  2. FASHION / CLOTHING
    1. DRESSES
    2. TROUSERS

WordPress Tags

WordPress’ tags suppose to be used to present more specific information about the post, usually content based. They are not mandatory. In WordPress each post/product must be assigned to at least one category, but no tags are required.

Query Categories and Tags in WordPress & WooCommerce

They are both used to group content. Both have archive pages, do list content assign to given category or tag. From a technical point of view, they are pretty much the same. From an WordPress application point of view, categories have native hierarchical structure where tags do not.

Both categories and tags are WP taxonomies. The strings describing the name of each category or tag are called WP terms.

SQL – lists all WooCommerce products and their tags

SELECT p.ID, p.post_title, tr.term_taxonomy_id, tt.taxonomy, tt.parent, tt.count, t.name, t.slug 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"
AND p.post_status = 'publish'
AND tt.taxonomy = "product_tag" -- <<<
ORDER BY p.post_title

SQL – lists all WooCommerce products and their categories

SELECT p.ID, p.post_title, tr.term_taxonomy_id, tt.taxonomy, tt.parent, tt.count, t.name, t.slug 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"
AND p.post_status = 'publish'
AND tt.taxonomy = "product_cat" -- <<<
ORDER BY p.post_title

The example above is general and represent database relations between tables. In WP database does not have constrains in a form of foreign keys defined.

It is important to understand that in WP, categories and tags are created with in a post type boundary. That said, we have a separate set of categories and tags for post_type=post and for post_type=product.

Also, the categories and tags are not directly related to each other. That said, to query all tags in a given category, we need to query both separately.

SQL – Query WooCommerce categories

If you wish to query only product categories, you could run a query joining two tables wp_terms, and wp_term_taxonomy. The same can be done for product tags, just change tt.taxonomy = "product_cat" to tt.taxonomy = "product_tag". Alternatively for regular WordPress post categories and tags you would use category and post_tag values instead.

-- 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;

If you would like to check if a given category has a child, you would run the fallowing query:

-- Check for direct children of given category ID
SELECT tt.taxonomy, tt.parent, tt.count, t.name, t.slug FROM wp_terms t
LEFT JOIN wp_term_taxonomy tt ON t.term_id = tt.term_taxonomy_id
WHERE tt.parent = 17 -- <<< replace with variable (parent ID)
ORDER BY tt.taxonomy;

SQL – Query all WooCommerce products tags

-- Query all tags
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_tag" -- <<<
-- AND tt.count = > 0 -- not empty
ORDER BY tt.taxonomy;

In the above query, we could also join in to wp_termmeta and filter only non-empty tags.
If this is what you’re looking for, read also this post: https://devwl.pl/woocommerce-sql-query-for-not-empty-tags/

SQL – Query all Product Tags with in specific Product category:

Let’s get to know the generic output first:

SELECT p.ID, p.post_title, tr.term_taxonomy_id, tt.taxonomy, tt.parent, tt.count, t.name, t.slug 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" -- <<< 
AND p.post_status = 'publish'
AND tt.taxonomy = "product_cat" -- <<<
AND t.name = "Owoce" -- tt.term_taxonomy_id = 18
ORDER BY p.ID;
  1. Query all published products in a category:
  2. Query all tags related to products found in previous query
  3. Use IN clause
-- To return all tags for given category:
-- select all products id with category X
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" -- <<< 
AND p.post_status = 'publish'
AND tt.taxonomy = "product_cat" -- <<<
AND t.name = "Owoce" -- tt.term_taxonomy_id = 18
ORDER BY p.ID;

-- select all tags assigned to a set of products
SELECT t.name, t.slug 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"
AND p.post_status = 'publish'
AND tt.taxonomy = "product_tag" -- <<<
AND p.ID IN (375, 374)
ORDER BY p.ID;

If the above query does not satisfy you, and you’re looking for a query that display all tags for a given category and its sub categories tree, read this post: https://devwl.pl/query-all-wordpress-tags-for-given-category-and-its-sub_categories-with-pure-sql/

Joining both above query into solution for WooCommerce:

The Query bellow allow for multiple category arguments

SET @CategoryByID = '18,17'; -- set correct category ID
-- SET @CategoryByName = 'TV, Radio'; -- set correct category name

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" -- <<< 
AND p.post_status = 'publish'
AND tt.taxonomy = "product_tag" -- <<<
AND p.ID IN
    (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" -- <<< 
    AND p.post_status = 'publish'
    AND tt.taxonomy = "product_cat" -- <<<
    AND FIND_IN_SET(tt.term_taxonomy_id, @CategoryByID) -- <<< search category by id
    -- AND FIND_IN_SET(t.name, @CategoryByName)  -- <<< search category by name
    ORDER BY p.ID)
ORDER BY p.ID

Solution for WordPress posts:

The example query below allow only for extracting tags only for single predefined category.

SET @CategoryByID = 1; -- set correct category ID
-- SET @CategoryByName = 'Politics'; -- set correct category name

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="post" -- <<< 
AND p.post_status = 'publish'
AND tt.taxonomy = "post_tag" -- <<< 
AND p.ID IN
    (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="post" -- <<< 
    AND p.post_status = 'publish'
    AND tt.taxonomy = "category" -- <<<
    AND tt.term_taxonomy_id = @CategoryByID -- <<< search category by id
    -- AND t.name = @CategoryByName  -- <<< search category by name
    ORDER BY p.ID)
ORDER BY p.ID

WordPress PHP get all tags of a specific category

<?php
$category="TV";
$custom_query = new WP_Query( 'posts_per_page=-1&category_name='.$category );
if ( $custom_query->have_posts() ) :
	while ( $custom_query->have_posts() ) : $custom_query->the_post();
		$posttags = get_the_tags();
		if ( $posttags ) {
			foreach( $posttags as $tag ) {
				$all_tags[] = $tag->term_id;
			}
		}
	endwhile;
endif;

$tags_arr = array_unique( $all_tags );
$tags_str = implode( ",", $tags_arr );

$args = array(
	'smallest'  => 12,
	'largest'   => 12,
	'unit'      => 'px',
	'number'    => 0,
	'format'    => 'list',
	'include'   => $tags_str
);
wp_tag_cloud( $args );
?>

by Josh Stauffer – src: https://www.joshstauffer.com/wordpress-get-tags-for-a-specific-category/

SQL WordPress POST – QUERY

The query below display each posts and list of its categories concatenated into a single comma separated string, same for assigned tags. It also presents a query for some imaginary post metadata.

SELECT DISTINCT
post_title, post_content
,(SELECT meta_value 
  	FROM wp_postmeta pm 
  	WHERE pm.meta_key = 'somemetakey' 
  	AND pm.post_id = p.ID
) AS "somemeta"
,(SELECT group_concat(t.name separator ', ') 
    FROM wp_terms t
    INNER JOIN wp_term_taxonomy on t.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships tr on tr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'category' and p.ID = tr.object_id
) AS "Categories"
,(SELECT group_concat(t.name separator ', ') 
    FROM wp_terms t
    INNER JOIN wp_term_taxonomy on t.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships tr on tr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'post_tag' and p.ID = tr.object_id
) AS "Tags"
FROM wp_posts p
WHERE post_type = 'post'
AND post_status = 'publish'
ORDER BY
post_title

SQL WooCommerce PRODUCT – QUERY

The query below display each product and list of its categories concatenated into a single comma separated string, same for assigned tags. It also presents a query for some imaginary post metadata.

SELECT DISTINCT
post_title, post_content
,(SELECT meta_value 
  	FROM wp_postmeta pm 
  	WHERE pm.meta_key = 'somemetakey' 
  	AND pm.post_id = p.ID
) AS "somemeta"
,(SELECT group_concat(t.name separator ', ') 
    FROM wp_terms t
    INNER JOIN wp_term_taxonomy on t.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships tr on tr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'product_cat' and p.ID = tr.object_id
) AS "Categories"
,(SELECT group_concat(t.name separator ', ') 
    FROM wp_terms t
    INNER JOIN wp_term_taxonomy on t.term_id = wp_term_taxonomy.term_id
    INNER JOIN wp_term_relationships tr on tr.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id
    WHERE taxonomy= 'product_tag' and p.ID = tr.object_id
) AS "Tags"
FROM wp_posts p
WHERE post_type = 'product'
AND post_status = 'publish'
ORDER BY
post_title

https://www.hostinger.com/tutorials/wordpress-tags
https://stackoverflow.com/questions/13071035/sql-query-to-extract-all-wordpress-posts-with-categories
https://stackoverflow.com/questions/31904758/woocommerce-get-product-tags-in-array
https://launchwoo.com.au/list-category-specific-product-tags-in-woocommerce/
https://wordpress.stackexchange.com/questions/212923/how-to-list-all-categories-and-tags-in-a-page

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x