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!
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.
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?
This doesn’t work when there are more than one ids in the array. 
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
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.