# Re: Find all items which have these tags

**URL:** <https://www.ruby-forum.com/t/re-find-all-items-which-have-these-tags/135230>\
**Category:** Rails\
**Created:** [April 19, 2008, 2:49pm UTC](https://www.ruby-forum.com/t/re-find-all-items-which-have-these-tags/135230 "2008-04-19T14:49:07Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![Tobi\_R](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/ea666f/32.png) [@Tobi\_R](https://www.ruby-forum.com/u/Tobi_R)\
**Post date:** [April 19, 2008, 2:49pm UTC](https://www.ruby-forum.com/t/re-find-all-items-which-have-these-tags/135230/1 "2008-04-19T14:49:07Z")

</div>

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://snippets.dzone.com/posts/show/34)  
[http://m.onkey.org/2007/11/1/find-users-with-at-least-n-items](http://m.onkey.org/2007/11/1/find-users-with-at-least-n-items)

–  
[http://www.rubynaut.com/](http://www.rubynaut.com/)