How to get the overall most popular tags from normalized tables?

Question

On my website, I have three big sections: a section where people can start discussions, a section where they can share stories and a blog with articles I write myself. I've build a custom tagging system using the database structure below.

In my tags_xref table I needed to have 3 different columns for each post type, because a discussion can have the same id as a story or article.

Everything is working exactly as it should, but I am having problems with queries to show certain tags. i already know how to show the most popular tags in the discussion section:

"SELECT t.id, t.tag, COUNT(discussion_id) AS cnt
    FROM tags_xref AS xrf
    INNER JOIN tags AS t ON xrf.tag_id = t.id
    GROUP BY t.id, t.tag
    ORDER BY COUNT(discussion_id) DESC LIMIT 20"

Now I want to show the most popular tags on the whole website, so from the discussions, stories and articles. Is there anyone who can help me with this? I have been working on this for hours and can't seem to find a solution.. Thanks!

Answer 1

You can do a simple modification to your query:

SELECT t.id, t.tag, COUNT(*) AS cnt
FROM tags_xref xrf INNER JOIN
     tags t
     ON xrf.tag_id = t.id
GROUP BY t.id, t.tag
ORDER BY COUNT(*) DESC
LIMIT 20;

Presumably, the various ids are NULL when they are not appropriate. If, for some strange reason, you actually stored values in all three ids for a given xref, you can do:

SELECT t.id, t.tag,
       (COUNT(story_id) + COUNT(discussion_id) + COUNT(article_id) ) AS cnt
FROM tags_xref xrf INNER JOIN
     tags t
     ON xrf.tag_id = t.id
GROUP BY t.id, t.tag
ORDER BY cnt DESC
LIMIT 20;