CODEWITHSUNDEEP
sqlruby-on-railsruby-on-rails-4rails-activerecordhas-and-belongs-to-many
Chris Keele
Chris Keele

Reputation: 3454

Query through HABTM with an exact set

In Rails 4, I have a vanilla has_and_belongs_to_many relationship, say between Cars and Colors.

Given a set of colors I'd like to find the cars that have exactly those colors. The closest I can get is: Car.joins(:colors).where('colors.id' => colors), but that returns all Cars with any of the colors.

I'd like to do this entirely in ActiveRecord since both tables are liable to be huge, so something like Car.joins(:colors).where('colors.id' => colors).to_a.select{|car| car.colors == colors} is less than ideal.

Any idea how to accomplish this?

Upvotes: 1

Views: 62

Answers (1)

Chris Keele
Chris Keele

Reputation: 3454

I was able to get it with having and some gnarly string interpolated SQL. I've made this into a scope you can use like so:

# Car.with_exact(colors: colors)

class ActiveRecord::Base
  class << self
    def with_exact(associations={})
      scope = self
      associations.each do |association_name, set|
        association = reflect_on_association(association_name)
        scope = scope.joins(association_name)
          .group("#{table_name}.id")
          .having("COUNT(\"#{association.join_table}\".*) = ?", set.count)
          .having(%{
            COUNT(\"#{association.join_table}\".*) = SUM(
              CASE WHEN \"#{association.join_table}\".\"#{association.association_foreign_key}\"
              IN (#{set.to_a.map(&:quoted_id).join(', ')})
              THEN 1 ELSE 0 END
            )
          }.squish)
      end
      scope
    end
  end
end

Upvotes: 1

Related Questions