当前位置: 动力学知识库 > 问答 > 编程问答 >

sql - 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?

网友答案:
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")
分享给朋友:
您可能感兴趣的文章:
随机阅读: