CODEWITHSUNDEEP
phpmysqltagging
Senne Vandenputte
Senne Vandenputte

Reputation: 539

Get most popular tags from mysql normalized tables

I'm building my own tag system for a forum I'm making. Everything is working perfectly, but I am trying to echo a list of the most popular tags but I can't find which query to use..

My tables look like this:

I need a list of the 20 most popular tags, so the tag_names of which the tag_id appear the most in the article_tag_xref table. Anyone who has an idea what the query should look like? Thanks!

Upvotes: 2

Views: 568

Answers (2)

SevenOfNine
SevenOfNine

Reputation: 648

The following should work out for you, as you only asked about the tag_names without their count. 

SELECT tag_name 
FROM Tag
WHERE tag_id IN ( SELECT tag_id, COUNT(article_id)
                  FROM Article_Tag_Xref
                  GROUP BY tag_id
                  ORDER BY COUNT(article_id) DESC
                  LIMIT 20)

The subquery returns the top 20 tag_ids and their count.

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72175

You can use the following query:

SELECT t.tag_id, t.tag_name, COUNT(article_id) AS cnt
FROM Article_Tag_Xref AS a
INNER JOIN Tag AS t ON a.tag_id = t.tag_id
GROUP BY t.tag_id, t.tag_name
ORDER BY COUNT(article_id) DESC LIMIT 20

COUNT(article_id) returns the number of appearances of each tag_ig in the Article_Tag_Xref table. Thus, order by this count in descending order and applying a LIMIT 20 returns the 20 most popular tag_ig values.

Upvotes: 4

Related Questions