[Ruby] Many to Many Query

[Laurel Fan]

On 1/29/07, Jordan Isip <jordanisip at yahoo.com> wrote:
> If I had a many to many relationship with posts and tags,
> what would be the best way to find all posts with a given
> array of tags?

I've ended up writing custom sql and using find_by_sql.  Something like this:

select u.* from users where users.id in (
select taggable_id
  from taggings tg
  inner join tags t on t.id = tg.tag_id and t.name in (:tags) and
tg.taggable_type = 'User'
  group by taggable_id
)

I'm using roughly the acts_as_taggable data model, so:

taggings is a table that has taggable_id, taggable_type, and tag_id
Tagging belongs_to taggable (polymorphic) and belongs_to tag

tags is a table with id and name
Tag belongs_to tag

(I cut and pasted from a bigger query and snipped lots of irrelevant
stuff.  and it's for oracle.  so this exact query probably won't
actually work for you)

So to call this you'd do something like:
User.find_by_sql([a_string_with_the_above_sql, {:tags => your_list_of_tags}])

It may be possible to convert this into a regular find() with a big
options hash.

I'd be curious to see if anyone has a more elegant way to do this...

-- 
Laurel Fan
http://blog.gorgorg.org