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.

score 22 · Answer 1 · answered May 06 '11 at 09:43

22

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

answered May 06 '11 at 09:43

Sam Figueroa

2,301
22
21

though LEFT OUTER JOIN is not the purpose of `includes`, it with the combination of a query will result in LEFT OUTER JOIN. – lulalala Jul 09 '12 at 10:17
2

This works, regardless of purpose. Less handwritten sql than accepted answer = I think it's better. – Matt De Leon Jul 22 '12 at 19:59

score 9 · Accepted Answer · edited Oct 06 '15 at 10:33

9

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.

edited Oct 06 '15 at 10:33

Ian Vaughan

20,211
13
59
79

answered Mar 16 '11 at 22:01

Mark Westling

5,904
4
26
30

Resulting sql that fails: SELECT persons.*, pets.id FROM `persons` left outer join pets where persons.id = pets.person_id WHERE `persons`.`deleted` = 0 AND (pets.id is null) – Shagymoe Mar 16 '11 at 23:32
Oops! I mistyped that join. It should have been "...on persons.id=pets.person_id", not "...where persons.id=...". – Mark Westling Mar 16 '11 at 23:55
Do you need the ",pets.id" in select('persons.*,pets.id')? It has the potential to collide with persons.id unless you alias the column like this: select('persons.*,pets.id as pet_id') – mkirk Apr 01 '11 at 20:16

score 5 · Answer 3 · answered Mar 17 '11 at 02:43

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

score 1 · Answer 4 · answered May 20 '14 at 09:34

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

score 0 · Answer 5 · edited Dec 03 '13 at 13:29

0

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

edited Dec 03 '13 at 13:29

Magne

16,401
10
68
88

answered Mar 17 '11 at 00:07

sirko

143
1
7

Finding records with no associated records in rails 3

5 Answers5

Linked