CODEWITHSUNDEEP
sqlruby-on-railsactiverecord
Nick Barrett
Nick Barrett

Reputation: 1051

ActiveRecord : find associated records with ALL conditions

I am trying to perform an activerecord query that essentially does the below.

I have 3 models in a many-to-many relationship

class Item < ActiveRecord::Base
    has_many :item_modifiers, dependent: :destroy
    has_many :modifiers, through: :item_modifiers
end

class ItemModifier < ActiveRecord::Base
    belongs_to :item
    belongs_to :modifier
end

class Modifier < ActiveRecord::Base
    has_many :item_modifiers
    has_many :items, through: :item_modifiers
end

Now I want to find all items that have modifiers with IDs 1 and 2

I have tried several things like:

Item.includes(:modifiers).where(modifiers: {id: 1}).where(modifiers: {id: 2})

This fails because it searches for modifiers where ID = 1 AND ID = 2 which is always false.

This also doesn't work

Item.includes(:modifiers).where(modifiers: {id: [1, 2]})

Because this does an IN (1, 2) query so it returns items with modifiers of either 1 or 2. I want items that have any modifiers as long as they have AT LEAST 1 modifier with ID 1 and AT LEAST 1 modifier with ID 2

I seem to be missing something quite simple but I just can't get my head around it.

Thanks in advance.

Upvotes: 0

Views: 182

Answers (1)

lalameat
lalameat

Reputation: 754

It could like: 

        Item.joins(:item_modifiers).where("item_modifiers.modifier_id=1 OR
 item_modifiers.modifier_id=2").group("items.id").having("COUNT(item_modifiers.id)=2")

If write in plain SQL, it could be:

  SELECT I.*, COUNT(IM.id) FROM items as I INNER JOIN item_modifiers AS IM on I.id=IM.item_id
 WHERE IM.modifier_id=1 OR IM.modifier_id=2 GROUP BY I.id HAVING COUNT(IM.id)=2

It will get all the items with its modifers' id include 1 and 2. Maybe in different DB, the statement need a slight change.

Upvotes: 1

Related Questions