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
--
http://www.rubynaut.com/
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---