SQL 查找关联计数大于零的所有记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20183710/
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
Find all records which have a count of an association greater than zero
提问by jphorta
I'm trying to do something that I thought it would be simple but it seems not to be.
我正在尝试做一些我认为很简单但似乎并非如此的事情。
I have a project model that has many vacancies.
我有一个有很多空缺的项目模型。
class Project < ActiveRecord::Base
has_many :vacancies, :dependent => :destroy
end
I want to get all the projects that have at least 1 vacancy. I tried something like this:
我想获得至少有 1 个空缺的所有项目。我试过这样的事情：
Project.joins(:vacancies).where('count(vacancies) > 0')
but it says
但它说
SQLite3::SQLException: no such column: vacancies: SELECT "projects".* FROM "projects" INNER JOIN "vacancies" ON "vacancies"."project_id" = "projects"."id" WHERE ("projects"."deleted_at" IS NULL) AND (count(vacancies) > 0).
SQLite3::SQLException: no such column: vacancies: SELECT "projects".* FROM "projects" INNER JOIN "vacancies" ON "vacancies"."project_id" = "projects"."id" WHERE ("projects"."deleted_at" IS NULL) AND (count(vacancies) > 0).
采纳答案by jvnill
joinsuses an inner join by default so using Project.joins(:vacancies)will in effect only return projects that have an associated vacancy.
joins默认情况下使用内部Project.joins(:vacancies)联接，因此使用实际上只会返回具有关联空缺的项目。
UPDATE:
更新：
As pointed out by @mackskatz in the comment, without a groupclause, the code above will return duplicate projects for projects with more than one vacancies. To remove the duplicates, use
正如@mackskatz 在评论中指出的那样，如果没有group子句，上面的代码将为具有多个空缺的项目返回重复的项目。要删除重复项，请使用
Project.joins(:vacancies).group('projects.id')
UPDATE:
更新：
As pointed out by @Tolsee, you can also use distinct.
正如@Tolsee 所指出的，您也可以使用distinct.
Project.joins(:vacancies).distinct
As an example
举个例子
[10] pry(main)> Comment.distinct.pluck :article_id
=> [43, 34, 45, 55, 17, 19, 1, 3, 4, 18, 44, 5, 13, 22, 16, 6, 53]
[11] pry(main)> _.size
=> 17
[12] pry(main)> Article.joins(:comments).size
=> 45
[13] pry(main)> Article.joins(:comments).distinct.size
=> 17
[14] pry(main)> Article.joins(:comments).distinct.to_sql
=> "SELECT DISTINCT \"articles\".* FROM \"articles\" INNER JOIN \"comments\" ON \"comments\".\"article_id\" = \"articles\".\"id\""
回答by Arta
1) To get Projects with at least 1 vacancy:
1) 要获得至少有 1 个职位空缺的项目：
Project.joins(:vacancies).group('projects.id')
2) To get Projects with more than 1 vacancy:
2) 要获得超过 1 个空缺的项目：
Project.joins(:vacancies).group('projects.id').having('count(project_id) > 1')
3) Or, if Vacancymodel sets counter cache:
3) 或者，如果Vacancy模型设置计数器缓存：
belongs_to :project, counter_cache: true
then this will work, too:
那么这也将起作用：
Project.where('vacancies_count > ?', 1)
Inflection rule for vacancymay need to be specified manually?
vacancy可能需要手动指定屈折规则？
回答by Peter Alfvin
Yeah, vacanciesis not a field in the join. I believe you want:
是的，vacancies不是在加入字段。我相信你想要：
Project.joins(:vacancies).group("projects.id").having("count(vacancies.id)>0")
回答by Dorian
# None
Project.joins(:vacancies).group('projects.id').having('count(vacancies) = 0')
# Any
Project.joins(:vacancies).group('projects.id').having('count(vacancies) > 0')
# One
Project.joins(:vacancies).group('projects.id').having('count(vacancies) = 1')
# More than 1
Project.joins(:vacancies).group('projects.id').having('count(vacancies) > 1')
回答by konyak
In Rails 4+, you can also use includesor eager_loadto get the same answer:
在 Rails 4+ 中，您还可以使用includes或aging_load来获得相同的答案：
Project.includes(:vacancies).references(:vacancies).
where.not(vacancies: {id: nil})
Project.eager_load(:vacancies).where.not(vacancies: {id: nil})
回答by Yuri Karpovich
I think there's a simpler solution:
我认为有一个更简单的解决方案：
Project.joins(:vacancies).distinct
回答by David Aldridge
Performing an inner join to the has_many table combined with a groupor uniqis potentially very inefficient, and in SQL this would be better implemented as a semi-join that uses EXISTSwith a correlated subquery.
对 has_many 表执行内部连接并结合 a grouporuniq可能非常低效，并且在 SQL 中，这将更好地实现为EXISTS与相关子查询一起使用的半连接。
This allows the query optimiser to probe the vacancies table to check for the existence of a row with the correct project_id. It doesn't matter whether there is one row or a million that have that project_id.
这允许查询优化器探测空缺表以检查是否存在具有正确 project_id 的行。无论是一行还是一百万具有该 project_id 都没有关系。
That's not as straightforward in Rails, but can be achieved with:
这在 Rails 中没有那么简单，但可以通过以下方式实现：
Project.where(Vacancies.where("vacancies.project_id = projects.id").exists)
Similarly, find all projects that have no vacancies:
同样，找到所有没有空缺的项目：
Project.where.not(Vacancies.where("vacancies.project_id = projects.id").exists)
Edit: in recent Rails versions you get a deprecation warning telling you to not to rely on existsbeing delegated to arel. Fix this with:
编辑：在最近的 Rails 版本中，您会收到一个弃用警告，告诉您不要依赖exists被委派给 arel。解决这个问题：
Project.where.not(Vacancies.where("vacancies.project_id = projects.id").arel.exists)
回答by konyak
Without much Rails magic, you can do:
没有太多的 Rails 魔法，你可以这样做：
Project.where('(SELECT COUNT(*) FROM vacancies WHERE vacancies.project_id = projects.id) > 0')
This type of conditions will work in all Rails versions as much of the work is done directly on the DB side. Plus, chaining .countmethod will work nicely too. I've been burned by queries like Project.joins(:vacancies)before. Of course, there are pros and cons as it's not DB agnostic.
这种类型的条件适用于所有 Rails 版本，因为大部分工作是直接在 DB 端完成的。另外，链接.count方法也能很好地工作。我一直被像Project.joins(:vacancies)以前一样的查询所困扰。当然，它有利有弊，因为它与数据库无关。
回答by wkhatch
The error is telling you that vacancies is not a column in projects, basically.
错误是告诉您，职位空缺基本上不是项目中的一栏。
This should work
这应该工作
Project.joins(:vacancies).where('COUNT(vacancies.project_id) > 0')
