How to do a query on has_and_belongs_to in Rails with AND across the join instead of OR?

user_1218280 0
Viewed 37

This may be a simple issue but I'm struggling. Here's what I'm trying to do:

I have a class, let's call it Car that HABTM Features. So:

class Car < ApplicationRecord
  has_and_belongs_to_many :features
end

class Feature < ApplicationRecord
  has_and_belongs_to_many :cars
end

Let's say I created the following data:

bumper = Feature.create(name: "Bumper")
windshield = Feature.create(name: "Windshield")
Car.create(name: "Toyota", features: [bumper, windshield])

So now I have a single car instance that has two features.

Let's say someone passes in (through a web form, for example) a list of features. I want to do a query to only find cars that have ALL the features passed in.

So if the feature list passed in was: ["Bumper", "Brakes"] then it should NOT find that "Toyota" car. But if the feature list was ["Bumper"] it should, or if the feature list was ["Windshield","Bumper"] it should.

This clearly doesn't work because it's an 'OR': Car.joins(:features).where(features: {name: ["Bumper","Brakes"]}) That returns the "Toyota" car because "Bumper" matches a feature joined to that car. What I want it to do, in this case, is return [] (no cars).

How do I set up a Rails query for this case?

Note: the number of features is variable for a given car and the number of feature lists passed in is also variable. I just want the passed-in feature list to be ANDed for the features and only return cars that have ALL the features based on the feature list.

I know this is probably easy but I can't seem to figure it out.

join has-and-belongs-to-many activerecord ruby-on-rails
1 Answers

Let's start from an easier case, Feature has a car_id field, Car has_many features.

Then you could query like this:

  1. Use ARRAY_AGG aggregate function to return a subquery, each row have all the feature names of a car, e.g. [{car_id: 1, names: ["Bumper", "Brakes"]}, ...]

  2. Car JOIN Feature by subquery, then filter cars with @> operator.

# Example code, may need to tweak a bit
sql = Feature.select(car_id, ARRAY_AGG(name) as names)
             .group(:car_id)
             .order(:car_id)
             .to_sql

Car.joins("INNER JOIN (#{sql}) AS features ON cars.id = features.user_id")
   .where("features.names @> ARRAY[?]::varchar[]", names)

In your case, you used has_and_belongs_to_many, so you need to tweak a bit to work with the joining table, e.g. car_features.

user_150970280
Related