# Problem with find condition and habtm relationship **URL:** \ **Category:** Rails\ **Created:** [August 21, 2006, 10:36pm UTC](https://www.ruby-forum.com/t/problem-with-find-condition-and-habtm-relationship/65855 "2006-08-21T22:36:28Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [August 21, 2006, 10:36pm UTC](https://www.ruby-forum.com/t/problem-with-find-condition-and-habtm-relationship/65855/1 "2006-08-21T22:36:28Z")
Ok. This has been troubling me for some time now. Please bare with me while I explain the situation to make things clear. I’m making a restaurant directory in RoR. I have a table of Restaurants and a table of Features which both have a habtm relationship with each other using a jointable called features\_restaurants which has the fields feature\_id and restaurant\_id. So restaurants can belong to many different features such as “vegitarian menu”, “child friendly” etc. In the view checkboxes are dynamically created for each feature using the following code: \<%= start\_form\_tag :action =\> ‘by\_feature’ %\> \<% for feature in @features %\> \<%= feature.name %\> \<% end %\> \<%= submit\_tag “Sort” %\> What I want to do is display only those restaurants that contain ALL the features checked by the user. So if the user checks the boxes “vegitarian menu” and “child friendly” the only restaurants that should show are those which belong to BOTH of those features. I was told that the following code would work: Restaurant.find(:all, :conditions =\> [ feature\_ids.map { |f| ’ feature\_id = ’ + f.to\_s }.join(’ AND ') ], :include =\> :features) But this doesn’t show anything when more than one checkbox is checked. I’ve tried so many different solutions and am getting frustrated. Thanks for reading all of this! ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [August 22, 2006, 1:32am UTC](https://www.ruby-forum.com/t/problem-with-find-condition-and-habtm-relationship/65855/2 "2006-08-22T01:32:47Z")
Someone must know ☹ This must have been done several times in other applications. ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [August 22, 2006, 6:07am UTC](https://www.ruby-forum.com/t/problem-with-find-condition-and-habtm-relationship/65855/3 "2006-08-22T06:07:07Z")
On Mon, 2006-08-21 at 20:14 +0000, removed\_email\_address@domain.invalid wrote: > dynamically created for each feature using the following code: > > But this doesn’t show anything when more than one checkbox is checked. > I’ve tried so many different solutions and am getting frustrated. > Thanks for reading all of this! * * * make it easy on yourself… ez\_where [http://brainspl.at/articles/2006/06/30/new-release-of-ez\_where-plugin](http://brainspl.at/articles/2006/06/30/new-release-of-ez_where-plugin) Craig ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [August 22, 2006, 4:34pm UTC](https://www.ruby-forum.com/t/problem-with-find-condition-and-habtm-relationship/65855/4 "2006-08-22T16:34:32Z")
OK. This may help, but it will also make your head hurt. The basic problem that you’re running into is that you want a “for all” operator and sql doesn’t have one. Instead, you have to think of the query in terms of the logically equivalent “not exists not”. Effectively, a proposition is true for all x if you can’t find a counter-example in x. In your case, a restaurant has all of the asked- for features if you can’t find one that it doesn’t have. It’s incredibly annoying to try to think of things this way, but it’s what SQL can do. Let’s assume, to save some typing, your tables are called rests, feats and rests2feats, and you’re looking for restaurants with a set of features (‘cheap’, ‘kid-friendly’). The SQL you want should go something like select r.rest from rests r where not exists (select \* from feats f where f.feat in (‘cheap’, ‘kid-friendly’) and not exists (select \* from rests2feats r2f where r.id = r2f.rest\_id and f.id = r2f.feat\_id)) The key is in the two nested selects. They are looking for a counter- example: a feat in (‘cheap’, ‘kid-friendly’) that your restaurant doesn’t have – ie, where there is no row in the habtm table linking your restaurant and that feature. The first not exists is there to catch those restaurants for which there is no such missing row – and which therefore have all of the features. I hope that this helps, and that it works for you. This is painful enough that I’m not entirely confident of the suggested code – though I am confident that the solution lies somewhere in that direction. If I could add one more suggestion, where I feel on much surer ground: vegetarian, one i, two e’s allbests, … … John B. ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [August 22, 2006, 6:11pm UTC](https://www.ruby-forum.com/t/problem-with-find-condition-and-habtm-relationship/65855/5 "2006-08-22T18:11:36Z")
Richard, I’ve posted a reply and a possible solution in your original thread entitled “Problem with find conditions” ---
**Author:** ![casper\_the\_ghost](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/58956e/32.png) [@casper\_the\_ghost](https://www.ruby-forum.com/u/casper_the_ghost)\ **Post date:** [September 4, 2006, 2:46pm UTC](https://www.ruby-forum.com/t/problem-with-find-condition-and-habtm-relationship/65855/6 "2006-09-04T14:46:05Z")
Hey, thanks very much for the help. Since posting I found my solution: def self.find\_by\_feature(feature\_ids, sort) return [] if feature\_ids.empty? feature\_ids = feature\_ids.flatten find(:all, :readonly =\> false, :joins =\> “INNER JOIN features\_restaurants a ON restaurants.id = a.restaurant\_id”, :conditions =\> [“a.feature\_id IN (?)”, feature\_ids], :group =\> “restaurants.id HAVING COUNT(restaurants.id) = #{feature\_ids.size}”) end Seems to work fine. I think I made an slight error describing what exactly I wanted to achieve. What this code achieves is displaying restaurants that include every checked box which I believe is more desirable behaviour.