Re: Find all items which have these tags

Hi

Daniel W. wrote:

Not sure if this will work, as I’m not very good at SQL anymore…

Item.find(:all, :include => :tags, :conditions => [ 'tags.name = ? AND
tags.name = ? ], ‘Rainproof’, ‘Bowl’ ])

Thanks, but this doesn’t work:

Item.find(:all, :include => :tags, :conditions => [‘tags.name = ?’,
‘Rainproof’]).map{|i|i.name}
=> [“Foo”, “Bar”]
Item.find(:all, :include => :tags, :conditions => [‘tags.name = ?’,
‘Bowl’]).map{|i|i.name}
=> [“Baz”, “Foo”]
Item.find(:all, :include => :tags, :conditions => [ 'tags.name = ? AND
tags.name = ? ', ‘Rainproof’, ‘Bowl’ ])
=> []

Here are some queries I came up with since the original post:

All recipes which have a tag “Quick” or a tag “Vegetarian”:

Item.find(:all, :include => ‘tags’, :conditions => [“tags.name in
(?)”,[‘Quick’,‘Vegetarian’]]).map{|i|i.name}
=> [“Cauliflower with Cheese Sauce”, “Veggies with Tomato Sauce”,
“Salami Pizza”, “Tofu with Veggies”]

All recipes which have a tag “Quick” and a tag “Vegetarian”:

tags = [“Vegetarian”,“Quick”]

Item.find(:all, :include => ‘tags’, :conditions => [“tags.name in
(?)”,tags], :group => “items.id having count(items.id) =
#{tags.length}”).map{|i|i.name}
=> [“Veggies with Tomato Sauce”, “Tofu with Veggies”]

Item.find_by_sql([“select items.* from items inner join items_tags on
items.id = items_tags.item_id inner join tags on items_tags.tag_id =
tags.id where tags.name in (?) group by items.id having count(items.id)
= ?”,tags,tags.length]).map{|i|i.name}
=> [“Veggies with Tomato Sauce”, “Tofu with Veggies”]

Item.find(:all, :joins => ‘inner join items_tags on items.id =
items_tags.item_id inner join tags on items_tags.tag_id = tags.id’,
:conditions => [“tags.name in (?)”,tags], :group => “items.id having
count(items.id) = #{tags.length}”).map{|i|i.name}
=> [“Veggies with Tomato Sauce”, “Tofu with Veggies”]

Perhaps anyone has a query that’s shorter, contains less SQL, and is
fast?
(No problem if not, AFAICS my new queries are OK.)

Thanks,
Tobi

P.S.
These helped:
http://snippets.dzone.com/posts/show/34
http://m.onkey.org/2007/11/1/find-users-with-at-least-n-items


http://www.rubynaut.com/

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs