0

Suppose I have three tables in my Rails app: cats, dogs, and owners. I want to find all the cats whose owners do not also have dogs.

With SQL, I could do the following:

SELECT
  `cats`.*
FROM
  `cats`
  LEFT JOIN `dogs` ON `cats`.`owner_id` = `dogs`.`owner_id`
WHERE
  `dogs`.`id` IS NULL;

However, I'd like to do this as a chainable scope on Cat. The closest I've gotten so far is Cat.connection.select_all(query_string), but that's not chainable.

What's the ActiveRecord way of doing this?

Nathan Long
  • 122,748
  • 97
  • 336
  • 451
  • 1
    I'm not sure if this helps but the relational operator you are describing is known as [semi difference (also antijoin)](http://stackoverflow.com/questions/7494586/get-non-existing-data-from-another-table-join/7496263#7496263). 'Search for nulls in [an outer] join' is but one way of writing semi difference using SQL, perhaps ActiveRecord has multiple ways too? – onedaywhen Jan 11 '12 at 16:16
  • @onedaywhen - interesting. Thanks. – Nathan Long Jan 11 '12 at 16:29

1 Answers1

2
Cat.joins("LEFT JOIN `dogs` ON `cats`.`owner_id` = `dogs`.`owner_id`").where("`dogs`.`id` IS NULL")

Or if you want it as a scope:

scope :cats_without_dogs, joins("LEFT JOIN `dogs` ON `cats`.`owner_id` = `dogs`.`owner_id`").where("`dogs`.`id` IS NULL")
PinnyM
  • 35,165
  • 3
  • 73
  • 81