Finding records with no associated records in rails 3

Question

class Person < ActiveRecord::Base
  has_many :pets

  scope :with_dog, join(:pets).where("pets.type = 'Dog'")
  scope :without_pets ???????????????????????????????????
end

class Pet < ActiveRecord::Base
  belongs_to :people
end

I'd like to add a scope to the Person model that returns people who have no pets. Any ideas? I feel like this is obvious, but it's escaping me at the moment.

Answer 1

Try something like this:

Person.joins('left outer join pets on persons.id=pets.person_id').
       select('persons.*,pets.id').
       where('pets.id is null')

I haven't tested it but it ought to work.

The idea is that we're performing a left outer join, so the pets fields will be null for every person that has no pets. You'll probably need to include :readonly => false in the join since ActiveRecord returns read-only objects when join() is passed a string.

Answer 2

scope :without_pets, lambda { includes(:pets).where('pets.id' => nil) }

Answer 3

Mark Westling's answer is correct. The outer join is the right way to go. An inner join (which is what the joins method generates if you pass it the name/symbol of an association and not your own SQL) will not work, as it will not include people who do not have a pet.

Here it is written as a scope:

scope :without_pets, joins("left outer join pets on pets.person_id = persons.id").where("pets.id is null")

(If that doesn't work, try replacing 'persons' with 'people' -- I'm not sure what your table name is.)