DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets has posted 5883 posts at DZone. View Full User Profile

Find Items With Similar (or As Many As Possible) Relationships - For A 'related Posts' Box Etc

04.03.2005
| 14551 views |
  • submit to reddit
        If we have the id for a post in <em>postid</em> and a limit of <em>num</em> and we want to find posts which share as many tags as possible with <em>postid</em>'s post, the following SQL will get you there.

SELECT p.*, COUNT(pt2.post_id) AS count FROM posts p, posts_tags pt, tags t, posts_tags pt2 WHERE pt.post_id=#{postid} AND t.id = pt.tag_id AND pt2.post_id != pt.post_id AND pt2.tag_id=pt.tag_id AND p.id = pt2.post_id GROUP BY pt2.post_id ORDER BY count DESC LIMIT #{num};")