CODEWITHSUNDEEP
ruby-on-railsruby-on-rails-4activerecord
wintermeyer
wintermeyer

Reputation: 8318

Where with a has_many through

A given Car has many features which are connected through listed_features. How can I find all cars which have the features with the ids 1 AND 2 AND 3? The cars can have more features but they have to have the first three features.

The setup:

rails g model Car name:string
rails g model Feature name:string
rails g model ListedFeature car:references feature:references

app/models/car.rb

class Car < ActiveRecord::Base
  has_many :listed_features
  has_many :features, through: :listed_features
end

app/models/listed_features.rb

class ListedFeature < ActiveRecord::Base
  belongs_to :car
  belongs_to :feature
end

app/models/features.rb

class Feature < ActiveRecord::Base
end

Upvotes: 2

Views: 67

Answers (1)

David Aldridge
David Aldridge

Reputation: 52336

The trick here may be to state that you need to get three matches from the features table.

id_list = [1, 2, 3]

Car.joins(:features).
    where(features: { id: id_list }).
    references(:features).
    group(:id).
    having("count(*) = ?", id_list.size)

Another approach would be to have a scope on Car for has_feature:

def self.has_feature(feature_id)
  where(CarFeature.where("car_features.car_id = cars.id and car_features.feature_id = ?", feature_id).exists)
end

You can then:

Car.has_feature(1).has_feature(2).has_feature(3)

... or make it more elegant with another class method that accepts an array of features and then applies the has_feature scope multiple times for you.

It would generate very efficient SQL.

Upvotes: 3

Related Questions