CODEWITHSUNDEEP
ruby-on-railsrubypostgresqlactiverecordscope
fedetaglia
fedetaglia

Reputation: 238

How to scope by associations

I have an issue with a query using Rails 4 and Postgres.

I have an object that have many options.

I would like to create a scope to select objects based on an array of options ids.

Something like:

scope :with_options, -> (option_ids) { joins(:options).where('options.id IN (?)', option_ids) }

But I do not need ANY of them, I need ALL of them.

ei.

If I've seleted the options with ids [2,3 ,4], I want to select all objects related with all these options ids, not only some of them.

I tried to check postgres_ext gem with contain operation but I couldn't have it working with associations.

Here is my structure: 

class MyObject < ActiveRecord::Base
  has_many :my_object_options
  has_many :options, through: :my_object_options
end

class MyObjectOption < ActiveRecord::Base
  belongs_to :my_object
  belongs_to :option
end

class Option < ActiveRecord::Base
  has_many :my_object_options
  has_many :my_object, through: :my_object_options
end

Any idea about how I can structure my scope?

Thanks

Edit:

I wrote a quick test that is passing with spickermann solution.

I paste here the description of the tests.

describe "#with_options" do
  context "passing 2 options" do
    context "a my_object associated with both" do
      it "should be included"
    end

    context "a my_object associated only with one of the two options" do
      it "should not be included"
    end

    context "a my_object associated with one of the two options and a third one" do
      it "should not be included"
    end
  end
end

Upvotes: 0

Views: 239

Answers (1)

spickermann
spickermann

Reputation: 106802

Try counting the number of associated options and return the ones that have a minimum number of matches:

scope :with_options, lambda { |option_ids|   
  joins(:options).
  where(options: {id: option_ids }).
  group('my_objects.id').
  having('COUNT(DISTINCT options.id) >= ?', option_ids.size)
}

Upvotes: 1

Related Questions