ActiveRecord: Exclude group if at least one record within it doesn't meet condition

Question

I have two models: an owner and a pet. An owner has_many :pets and a pet belongs_to :owner.

What I want to do is grab only those owners that have pets which ALL weigh over 30lbs.

#app/models/owner.rb
class Owner < ActiveRecord::Base
  has_many :pets
  #return only those owners that have heavy pets
end

#app/models/pet.rb
class Pet < ActiveRecord::Base
  belongs_to :owner
  scope :heavy, ->{ where(["weight > ?", 30])}
end

Here is what is in my database. I have three owners:

1. Neil, and ALL of which ARE heavy;
2. John, and ALL of which ARE NOT heavy;
3. Bob, and SOME of his pets ARE heavy and SOME that ARE NOT heavy.

The query should return only Neil. Right now my attempts return Neil and Bob.

Answer 1

You can form a group for each owner_id and check, if all rows within group match required condition or at least one row doesn't match it, you can achieve it with group by and having clauses:

scope :heavy, -> { group("owner_id").having(["count(case when weight <= ? then weight end) = 0", 30]) }

There is also another option, more of a Rails-ActiverRecord approach:

scope :heavy, -> { where.not(owner_id: Pet.where(["weight <= ?", 30]).distinct.pluck(:owner_id)).distinct }

Here you get all owner_ids that don't fit condition (searching by contradiction) and exclude them from the result of original query.

Answer 2

Isn't this simply a matter of finding the owners for whom the minimum pet weight is greater than some value:

scope :heavy, -> { group("owner_id").joins(:pets).having("min(pets.weight) >= ?", 30)}

Or conversely,

scope :light, -> { group("owner_id").joins(:pets).having("max(pets.weight) < ?", 30)}

These are scopes on the Owner, by the way, not the Pet

Another approach is to turn this into a scope on Owner:

Owner.where(Pet.where.not("pets.owner_id = owners.id and pets.weight < ?", 30).exists)

Subtly different, as it is checking for the non-existence of a per with a weight less than 30, so if an owner has no pets then this condition will match for that owner.

In database terms, this is going to be the most efficient query for large data sets.

Indexing of pets(owner_id, weight) is recommended for both these approaches.