CODEWITHSUNDEEP
sqlactiverecordjoin
Nathan Long
Nathan Long

Reputation: 125902

What's the ActiveRecord way to search for nulls in a join?

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?

Upvotes: 0

Views: 151

Answers (1)

PinnyM
PinnyM

Reputation: 35533

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

Upvotes: 2

Related Questions