# Problem with find conditions **URL:** \ **Category:** Rails\ **Created:** [August 21, 2006, 4:57pm UTC](https://www.ruby-forum.com/t/problem-with-find-conditions/65811 "2006-08-21T16:57:08Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![richard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/f0a364/32.png) [@richard](https://www.ruby-forum.com/u/richard)\ **Post date:** [August 21, 2006, 4:57pm UTC](https://www.ruby-forum.com/t/problem-with-find-conditions/65811/1 "2006-08-21T16:57:08Z")
def self.find\_by\_feature(feature\_ids, sort) Restaurant.find(:all, :conditions =\> [ "feature\_id IN (?) ", feature\_ids ], :include =\> :features, :order =\> “feature\_id DESC”) end I have this code. feature\_ids is an array of ids and feature\_id is a column in a join-table between restaurants and features which are in a habtm relationship. What I want to know is how to find only restaurants which have all features in the feature\_ids array. At the moment I think that code just sees if one feature is in the array. The reason for this is because I want to be able to find Restaurants who belong to ALL of some specified features, not restaurants that belong to one of the specified features. This had been bugging me for days now and I will really appreciate the help! ---
**Author:** ![richard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/f0a364/32.png) [@richard](https://www.ruby-forum.com/u/richard)\ **Post date:** [August 21, 2006, 6:16pm UTC](https://www.ruby-forum.com/t/problem-with-find-conditions/65811/2 "2006-08-21T18:16:26Z")
Richard wrote: > def self.find\_by\_feature(feature\_ids, sort) > Restaurant.find(:all, :conditions =\> [ "feature\_id IN (?) ", > feature\_ids ], :include =\> :features, :order =\> “feature\_id DESC”) > end For case of finding any included feature, shouldn’t the condition be the following? :conditions =\> ["feature\_id IN (?) ", feature\_ids.join(’,’)] > What I want to know is how to find only restaurants which have all > features in the feature\_ids array. At the moment I think that code just > sees if one feature is in the array. :conditions =\> [feature\_ids.map { |f| ’ feature\_id = ’ + f.to\_s }.join(’ AND ')] If feature\_ids is [1,2,3], then this string will be generated for the condition: " feature\_id = 1 AND feature\_id = 2 AND feature\_id = 3" But what do your tables look like? I’m assuming restaurants and features are many-to-many, but I don’t see the table that joins them together anywhere–which is the one you would need to do the feature\_id query on. ---
**Author:** ![richard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/f0a364/32.png) [@richard](https://www.ruby-forum.com/u/richard)\ **Post date:** [August 21, 2006, 6:23pm UTC](https://www.ruby-forum.com/t/problem-with-find-conditions/65811/3 "2006-08-21T18:23:46Z")
Curtis S. wrote: > Richard wrote: > > > def self.find\_by\_feature(feature\_ids, sort) > > Restaurant.find(:all, :conditions =\> [ "feature\_id IN (?) ", > > feature\_ids ], :include =\> :features, :order =\> “feature\_id DESC”) > > end > > For case of finding any included feature, shouldn’t the condition be the > following? > > :conditions =\> ["feature\_id IN (?) ", feature\_ids.join(’,’)] > > > What I want to know is how to find only restaurants which have all > > features in the feature\_ids array. At the moment I think that code just > > sees if one feature is in the array. > > :conditions =\> > [feature\_ids.map { |f| ’ feature\_id = ’ + f.to\_s }.join(’ AND ')] > > If feature\_ids is [1,2,3], then this string will be generated for the > condition: > > " feature\_id = 1 AND feature\_id = 2 AND feature\_id = 3" > > But what do your tables look like? I’m assuming restaurants and > features are many-to-many, but I don’t see the table that joins them > together anywhere–which is the one you would need to do the feature\_id > query on. Yes it is a many to many relationship using a jointable called features\_restaurants which has the fields feature\_id and restaurant\_id. So does the code you have given me see if the restaurant has EVERY feature that the user checks? ---
**Author:** ![richard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/f0a364/32.png) [@richard](https://www.ruby-forum.com/u/richard)\ **Post date:** [August 21, 2006, 9:54pm UTC](https://www.ruby-forum.com/t/problem-with-find-conditions/65811/4 "2006-08-21T21:54:09Z")
This doesn’t work when there are more than one ids in the array. ☹ ---
**Author:** ![richard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/f0a364/32.png) [@richard](https://www.ruby-forum.com/u/richard)\ **Post date:** [August 22, 2006, 9:43am UTC](https://www.ruby-forum.com/t/problem-with-find-conditions/65811/5 "2006-08-22T09:43:25Z")
Curtis S. wrote: > For case of finding any included feature, shouldn’t the condition be the > following? > > :conditions =\> ["feature\_id IN (?) ", feature\_ids.join(’,’)] ActiveRecord does that automagically for you if you pass it an array. Chris ---
**Author:** ![richard](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/f0a364/32.png) [@richard](https://www.ruby-forum.com/u/richard)\ **Post date:** [August 22, 2006, 6:08pm UTC](https://www.ruby-forum.com/t/problem-with-find-conditions/65811/6 "2006-08-22T18:08:50Z")
> ActiveRecord does that automagically for you if you pass it an array. Awesome! Richard, I think I have a better understanding of what you are wanting now. It’s a difficult problem, but let’s see if this is a step in the right direction. If we could get a count of the features matched per restaurant, then we could rank the restaurants by number of matches. If the number of matches equals the feature count specified, then we have a 100% match. Let’s do this in SQL: Product.find\_by\_sql( [“SELECT count(restaurants.id) AS num\_matched, restaurants.\* FROM restaurants INNER JOIN features\_restaurants ON (restaurants.id = features\_restaurants.restaurant\_id) WHERE features\_restaurants.features\_id IN (?) GROUP BY restaurants.id ORDER BY num\_matched desc”, feature\_ids]) The num\_matched field would contain the number of features matched per restaurant. You could then show closest matches first and even calculate a percentage with num\_matched/feature\_ids.size. Or, if you are only interested in the 100% matches, you can adding a HAVING clause between the group by and order by: HAVING count(restaurants.id) = #{feature\_ids.size.to\_s} Try this and see how it works for you.