Finding records with no associated records in rail

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

You must use a LEFT OUTER JOIN in order to find records without associated records. Here's an adapted version of a code I use:

scope :without_pets, joins('LEFT OUTER JOIN pets ON people.id = pets.person_id').group('people.id').having('count(pets.id) = 0')

Answer 2

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 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.)

Answer 4

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

Answer 5

Im not sure if your pet model has a person id, but maybe this attempt helps you somehow

scope :with_dog, joins(:pets).where("pets.type = 'Dog'")
scope :without_pets, joins(:pets).where("pets.person_id != persons.id")

Update: Corrected the query method name from 'join' to 'joins'.