从mysql规范化表中获取最流行的标签

Question

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!

Answer 1

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.