Hi
Daniel Waite 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