[Ruby] Many to Many Query

[John Wulff]

Something like this avoids the evil find_by_sql.

tags = Tag.find :all, :conditions => ['tags.id = ? or tags.id = ?',
tag_1.id, tag_2.id], :include => :pages
pages = []
for tag in tags
  pages += tag.pages
end

On 1/29/07, Laurel Fan <laurel.fan at gmail.com> wrote:
>
> 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
> _______________________________________________
> Ruby at zenspider.com - Seattle.rb non-commercial list
> http://www.zenspider.com/seattle.rb
> http://www.zenspider.com/mailman/listinfo/ruby
>



-- 
John Wulff    360.317.6447