SQL Rails 查找相关联的 has_many 记录为零的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9613717/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Rails find record with zero has_many records associated
提问by Andrew
This seems fairly simple but I can't get it to turn up on Google.
这看起来相当简单，但我无法在 Google 上显示它。
If I have:
如果我有：
class City < ActiveRecord::Base
has_many :photos
end
class Photo < ActiveRecord::Base
belongs_to :city
end
I want to find all cities that have no photos. I'd love to be able to call something like...
我想找到所有没有照片的城市。我很想能够调用类似...
City.where( photos.empty? )
...but that doesn't exist. So, how do you do this kind of query?
……但那不存在。那么，你如何进行这种查询呢？
Update:Having now found an answer to the original question, I'm curious, how do you construct the inverse?
更新：现在找到了原始问题的答案，我很好奇，你如何构造逆？
IE: if I wanted to create these as scopes:
IE：如果我想将这些创建为范围：
scope :without_photos, includes(:photos).where( :photos => {:city_id=>nil} )
scope :with_photos, ???
回答by Andrew
Bah, found it here: https://stackoverflow.com/a/5570221/417872
呸，在这里找到：https: //stackoverflow.com/a/5570221/417872
City.includes(:photos).where(photos: { city_id: nil })
回答by TeWu
In Rails 5, to find all cities that have no photos, you can use left_outer_joins:
在Rails 5 中，要查找所有没有照片的城市，您可以使用left_outer_joins：
City.left_outer_joins(:photos).where(photos: {id: nil})
which will result in SQL like:
这将导致 SQL 如下：
SELECT cities.*
FROM cities LEFT OUTER JOIN photos ON photos.city_id = city.id
WHERE photos.id IS NULL
Using includes:
使用includes：
City.includes(:photos).where(photos: {id: nil})
will have the same result, but will result in much uglier SQL like:
将有相同的结果，但会导致更丑陋的 SQL，如：
SELECT cities.id AS t0_r0, cities.attr1 AS t0_r1, cities.attr2 AS t0_r2, cities.created_at AS t0_r3, cities.updated_at AS t0_r4, photos.id AS t1_r0, photos.city_id AS t1_r1, photos.attr1 AS t1_r2, photos.attr2 AS t1_r3, photos.created_at AS t1_r4, photos.updated_at AS t1_r5
FROM cities LEFT OUTER JOIN photos ON photos.city_id = cities.id
WHERE photos.id IS NULL
回答by Yossi Shasho
When trying to find records with no matching records from the joined table, you need to use a LEFT OUTER JOIN
当尝试从连接表中查找没有匹配记录的记录时，您需要使用 LEFT OUTER JOIN
scope :with_photos, joins('LEFT OUTER JOIN photos ON cities.id = photos.city_id').group('cities.id').having('count(photos.id) > 0')
scope :without_photos, joins('LEFT OUTER JOIN photos ON cities.id = photos.city_id').group('cities.id').having('count(photos.id) = 0')
回答by Onikoroshi
I used a join to get all the ones withphotos:
我使用 join 来获取所有带照片的照片：
scope :with_photos, -> { joins(:photos).distinct }
scope :with_photos, -> { joins(:photos).distinct }
Easier to write and understand, for that particular case. I'm not sure what the efficiency is of doing a join vs doing an includes, though
对于这种特殊情况，更容易编写和理解。不过，我不确定执行 join 与执行包含的效率如何
回答by skepticscript
I don't believe the accepted answer gives you exactly what you're looking for, as you want to do a LEFT OUTER JOINand that answer will give you a INNER JOIN. At least in Rails 5 you can use:
我不相信接受的答案会给你你正在寻找的东西，因为你想做 aLEFT OUTER JOIN而那个答案会给你一个INNER JOIN. 至少在 Rails 5 中你可以使用：
scope :without_photos, left_joins(:photos).where( photos: {id: nil} )
or you can use mergein cases where namespacing will make the whereclause cumbersome:
或者您可以merge在命名空间会使where子句变得麻烦的情况下使用：
scope :without_photos, left_joins(:photos).merge( Photos.where(id: nil) )
回答by RaphaMex
If you are not running Rails 5+ and performance is a must-have, avoid useless ActiveRecord creation and get just what you need:
如果您运行的不是 Rails 5+ 并且性能是必须的，请避免无用的 ActiveRecord 创建并获得您需要的：
City.where("NOT EXISTS(SELECT 1 FROM photos WHERE photos.city_id = cities.id LIMIT 1)")
