A story of tags and categories:

WordPress 2.3 brought tags to WordPress. At first this seemed quite unnecessary as WordPress already had categories to organize blogposts. I recently discovered categories can be quite handy to make a broad division between different kind of posts, say a news snippet versus an essay, while tags provide a quick way to mark posts. WordPress even has a handy wp_tag_cloud()-function to show the tags you use in a fashionable way.

There’s only one problem: the tagcloud is based on all posts. Fine and dandy on the frontpage of your blog, but a lot less usefull on category pages. There is no easy way to generate a tagcloud for all posts belonging to a specific category. I personaly need something like this for FiviKASKA.be, since I want a clear distinction between news and movies on that site. So I started tinkering.

Here’s the query you need to get a list of all tags in one category, and their postcounts.

SELECT tags.term_id AS tag_ID, 
   tags.name AS tag_name, 
   tags.slug AS tag_slug, 
   count(wp_posts.ID) AS postcount
FROM wp_terms AS tags
INNER JOIN wp_term_taxonomy AS tag_taxonomy 
   ON tags.term_id = tag_taxonomy.term_id 
   AND tag_taxonomy.taxonomy = 'post_tag'
INNER JOIN wp_term_relationships AS tag_relationships 
   ON tag_taxonomy.term_taxonomy_id = tag_relationships.term_taxonomy_id
INNER JOIN wp_posts ON wp_posts.ID = tag_relationships.object_id
INNER JOIN wp_term_relationships AS cat_relationships 
   ON wp_posts.ID = cat_relationships.object_ID
INNER JOIN wp_term_taxonomy AS cat_taxonomy 
   ON cat_relationships.term_taxonomy_id = cat_taxonomy.term_taxonomy_id 
   AND cat_taxonomy.taxonomy = 'category'
INNER JOIN wp_terms AS categories 
   ON cat_taxonomy.term_id = categories.term_id
WHERE wp_posts.post_type = 'post' 
   AND wp_posts.post_status = 'publish'
   AND categories.term_id = <your_category_id>
GROUP BY tag_ID
ORDER BY postcount DESC

WordPress 2.3 or higher

Replace <your_category_id> with the ID of the category you’re interested in, or use categories.name = <my_category_name>.

I used a lot of table aliasing in an effort to make this query a bit more understandable. WordPress uses a set of 3 tables (wp_terms, wp_term_taxonomy, wp_term_relationships) to store link categories, tags and categories all mixed up. This query must link tags and categories over the posts table so it has to visit this set of 3 tables twice, hence the aliasing. The query will look at all tags, find their associated posts and see if these posts are in the category you want. I’m not making any claims about its performance…