Over a million developers have joined DZone.

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

·
If we have the id for a post in postid and a limit of num and we want to find posts which share as many tags as possible with postid'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};")
Topics:

The best of DZone straight to your inbox.

SEE AN EXAMPLE
Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.
Subscribe

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}