Will Will - 1 year ago 68
SQL Question

Active Record query to find records that match all conditions in Rails has_many through relationship

I have two models, Apartments and Amenities, which are associated through ApartmentAmenities. I am trying to implement a filter where I only show apartments that have all of the amenities specified.

class Amenity < ActiveRecord::Base
has_many :apartment_amenities
has_many :apartments, through: :apartment_amenities

class ApartmentAmenity < ActiveRecord::Base
belongs_to :apartment
belongs_to :amenity

class Apartment < ActiveRecord::Base
has_many :apartment_amenities
has_many :amenities, through: :apartment_amenities

I've got a query working that will return all apartments that match at least one of the amenities of given set like so:

Apartment.joins(:apartment_amenities).where('apartment_amenities.amenity_id IN (?)', [1,2,3])

but this isn't quite what I'm going for.

Answer Source

Alright, after giving up for a few days then getting back to it, I finally found this question: How to find records, whose has_many through objects include all objects of some list?

Which led me to the answer that works properly:

  def self.with_amenities(amenity_ids)
    where("NOT EXISTS (SELECT * FROM amenities
      WHERE NOT EXISTS (SELECT * FROM apartment_amenities
        WHERE apartment_amenities.amenity_id = amenities.id
        AND apartment_amenities.apartment_id = apartments.id)
      AND amenities.id IN (?))", amenity_ids)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download