Rails - Aug 2006 - Re: Problem with find condition and habtm relationship.

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



On 21 Aug 2006, at 21:14, novaprospekt-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
wrote:
>
> 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!
>
>
> >
>


--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---