Search code examples
mysqltagssimilarity

Mysql, similar topics

Hy guys!

I have 3 tables: 

topics
(id*, title, body) 

tags
(id*,name)

topic_tags
(topic_id,tag_id)

Every topic has some tags. I want to find the "most related" topics to another one, based just on tags (and not based on the title and body of the topics).

If more common tags 2 topics have, they are more related. I want to order the results somehow by similarity (= number of common tags).

What do you suggest?

Solution 
  • SELECT  tr.*
FROM    topic t
JOIN    topic_tag tt
ON      tt.topic_id = t.id
JOIN    topic_tag ttr
ON      ttr.tag_id = tt.tag_id
JOIN    topic tr
ON      tr.id = ttr.topic_id
WHERE   t.id = $topicid
GROUP BY
        tr.id
ORDER BY
        COUNT(*) DESC