Problem with find condition and habtm relationship.

rubyonrails-talk
1

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 %>     <input type="checkbox"            id="<%= feature.id %>"            name="feature_ids"            value="<%= feature.id %>"     > <%= feature.name %><br />   <% 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!

2

Someone must know :frowning: This must have been done several times in other applications.

3

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 %>     <input type="checkbox"            id="<%= feature.id %>"            name="feature_ids"            value="<%= feature.id %>"     > <%= feature.name %><br />   <% 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!

4

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 Browning

5

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.