Filtering model with HABTM relationship

Question

I have 2 models - Restaurant and Feature. They are connected via has_and_belongs_to_many relationship. The gist of it is that you have restaurants with many features like delivery, pizza, sandwiches, salad bar, vegetarian option,… So now when the user wants to filter the restaurants and lets say he checks pizza and delivery, I want to display all the restaurants that have both features; pizza, delivery and maybe some more, but it HAS TO HAVE pizza AND delivery.

If I do a simple .where('features IN (?)', params[:features]) I (of course) get the restaurants that have either - so or pizza or delivery or both - which is not at all what I want.

My SQL/Rails knowledge is kinda limited since I'm new to this but I asked a friend and now I have this huuuge SQL that gets the job done:

Restaurant.find_by_sql(['SELECT restaurant_id FROM (
                                                  SELECT features_restaurants.*, ROW_NUMBER() OVER(PARTITION BY restaurants.id ORDER BY features.id) AS rn FROM restaurants
                                                  JOIN features_restaurants ON restaurants.id = features_restaurants.restaurant_id
                                                  JOIN features ON features_restaurants.feature_id = features.id
                                                  WHERE features.id in (?)
                                                ) t
                                                WHERE rn = ?', params[:features], params[:features].count])

So my question is: is there a better - more Rails even - way of doing this? How would you do it?

Oh BTW I'm using Rails 4 on Heroku so it's a Postgres DB.

Answer 1

This is an example of a set-iwthin-sets query. I advocate solving these with group by and having, because this provides a general framework.

Here is how this works in your case:

select fr.restaurant_id
from features_restaurants fr join
     features f
     on fr.feature_id = f.feature_id
group by fr.restaurant_id
having sum(case when f.feature_name = 'pizza' then 1 else 0 end) > 0 and
       sum(case when f.feature_name = 'delivery' then 1 else 0 end) > 0

Each condition in the having clause is counting for the presence of one of the features -- "pizza" and "delivery". If both features are present, then you get the restaurant_id.

Answer 2

This is not "copy and paste" solution but if you consider following steps you will have fast working query.

• index feature_name column (I'm assuming that column feature_id is indexed on both tables)

• place each feature_name param in exists():

  select fr.restaurant_id
  from
      features_restaurants fr
  where
      exists(select true from features f where fr.feature_id = f.feature_id and f.feature_name = 'pizza') 
      and
      exists(select true from features f where fr.feature_id = f.feature_id and f.feature_name = 'delivery')
  group by 
      fr.restaurant_id
  

Answer 3

Maybe you're looking at it backwards?

Maybe try merging the restaurants returned by each feature.

Simplified:

pizza_restaurants = Feature.find_by_name('pizza').restaurants
delivery_restaurants = Feature.find_by_name('delivery').restaurants

pizza_delivery_restaurants = pizza_restaurants & delivery_restaurants

Obviously, this is a single instance solution. But it illustrates the idea.

UPDATE

Here's a dynamic method to pull in all filters without writing SQL (i.e. the "Railsy" way)

def get_restaurants_by_feature_names(features)
  # accepts an array of feature names
  restaurants = Restaurant.all
  features.each do |f|
    feature_restaurants = Feature.find_by_name(f).restaurants
    restaurants = feature_restaurants & restaurants
  end

  return restaurants
end

Answer 4

How much data is in your features table? Is it just a table of ids and names?

If so, and you're willing to do a little denormalization, you can do this much more easily by encoding the features as a text array on restaurant.

With this scheme your queries boil down to

select * from restaurants where restaurants.features @> ARRAY['pizza', 'delivery']

If you want to maintain your features table because it contains useful data, you can store the array of feature ids on the restaurant and do a query like this:

select * from restaurants where restaurants.feature_ids @> ARRAY[5, 17]

If you don't know the ids up front, and want it all in one query, you should be able to do something along these lines:

select * from restaurants where restaurants.feature_ids @> (
  select id from features where name in ('pizza', 'delivery')
) as matched_features

That last query might need some more consideration...

Anyways, I've actually got a pretty detailed article written up about Tagging in Postgres and ActiveRecord if you want some more details.

Answer 5

Since its an AND condition (the OR conditions get dicey with AREL). I reread your stated problem and ignoring the SQL. I think this is what you want.

# in Restaurant
has_many :features

# in Feature
has_many :restaurants

# this is a contrived example. you may be doing something like 
# where(name: 'pizza'). I'm just making this condition up. You
# could also make this more DRY by just passing in the name if 
# that's what you're doing. 

def self.pizza
  where(pizza: true) 
end

def self.delivery
  where(delivery: true)  
end 

# query 
Restaurant.features.pizza.delivery

Basically you call the association with ".features" and then you use the self methods defined on features. Hopefully I didn't misunderstand the original problem.

Cheers!

Answer 6

Restaurant
  .joins(:features)
  .where(features: {name: ['pizza','delivery']})
  .group(:id)
  .having('count(features.name) = ?', 2)

This seems to work for me. I tried it with SQLite though.