Never been to DZone Snippets before?

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

About this user

Peter Cooperx http://www.petercooper.co.uk/

« Newer Snippets
Older Snippets »
Showing 1-4 of 4 total  RSS 

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};")
to sql tags tagging snippets by peter on Apr 03, 2005

Find all many-to-many relationships which are tied to an arbitrary number of other many-to-many relationships

Yet more code from Snippets itself. When you narrow down posts by tags, I only want you to see tags which are shared by other posts which also have the same current tags. This is more difficult than it sounds, and requires use of a subselect:

SELECT *, COUNT(pt.post_id) AS count FROM posts_tags pt, tags t WHERE pt.post_id IN (SELECT pt.post_id FROM posts_tags pt, tags t WHERE pt.tag_id = t.id AND (t.name IN ('rails', 'ruby')) GROUP BY pt.post_id HAVING COUNT(pt.post_id)=2) AND t.id = pt.tag_id GROUP BY pt.tag_id ORDER BY count DESC;

The first query (inside the subselect) finds all post IDs for posts which definitely contain any of the current tags. The outer query finds all OTHER tags associated with these posts, therefore we find all (and the quantity of) the tags related to the input tags.
to sql tags tagging snippets by peter on Apr 03, 2005

Return items with data intersections over a many-to-many join

If you have three tables, 'posts', 'tags', and a join table 'posts_tags', and you wish to find all posts which are all associated with a number of different tags, you can use this SQL trick:

SELECT p.* FROM posts_tags pt, posts p, tags t WHERE pt.tag_id = t.id AND (t.name IN ('tag1', 'tag2', 'tag3')) AND p.id=pt.post_id GROUP BY p.id HAVING COUNT(p.id) = 3;

.. where 3 is the number of tags in total. In Ruby/Rails, if you have an array called 'tags' containing the tags, you could use this code:

@posts = Post.find_by_sql ("SELECT p.* FROM posts_tags pt, posts p, tags t WHERE pt.tag_id = t.id AND (t.name = '" + tags.uniq.join ('\' OR t.name=\'') + "') AND p.id=pt.post_id GROUP BY p.id HAVING COUNT(p.id) = " + tags.uniq.length.to_s)


This is how Snippets itself works. You can also add a p.user_id check to the HAVING operator to only find posts with certain tags by a certain user.
to ruby rails sql tags tagging by peter on Apr 03, 2005

take tag names and return an array of tag objects

@params[:tags].map do |tag|
	Tag.find_by_name (tag) rescue nil
end
to ruby rails tags by peter on Apr 02, 2005
« Newer Snippets
Older Snippets »
Showing 1-4 of 4 total  RSS