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