Mysql: Finding most relevant links with tags >> Scott Klarr

Previous Post: Tip of the day: Locking your computer from being used
Next Post: Battle of the operating systems! Part 1: Intro

Mysql: Finding most relevant links with tags

Posted By Scott Klarr on Dec 21, 2007 at 2:43 pm

The Problem

While writing the backend for this blog, I needed to be able to select the most relevant topics to display at the bottom of each post based on shared tags. It is a little more complex than standard select queries so I figured I would post it up here in case someone happens to be searching for this particular solution.

The database is setup so that there is a topics table, which obviously contains all the topics, and then a separate table for tags. The tags table has data columns id, title, and topic which are pretty self explanatory. The tags are stored as 1 row for each tag for each topic; so if one topic has 10 tags, there are 10 rows for that topic within the tags table.

The MySQL Query


SELECT 
  tags.topic, 
  COUNT(*) as total, 
  topics.*

FROM 
  tags 
  JOIN topics

ON 
  topics.id=tags.topic AND
  (TAGLIST) AND
  tags.topic!='ID'

GROUP BY 
  tags.topic 

ORDER BY 
  total DESC 

LIMIT 7

TAGLIST is replaced by the list of tags of the current topic to search for with an OR operator between them.
For example: (tags.title='linux' OR tags.title='css' OR tags.title='software')

ID is replaced by the current topic ID, just to keep the current topic from showing up in the related links list.

How Does It Work?

In a nutshell, what this query does is look for any tags that match any of the given tags (from the current topic), groups them by topic id, orders them by the number of matching tags per topic, and returns the top 7.

If you find this useful, please leave a comment below!

Post Comment

Your Name
Email Address
Website URL
Comment