Find all records which have a count of an association greater than zero

我试着做一些我以为会很简单的事情，但似乎并不简单。

我有一个项目模型，有很多空缺。

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:

Project.joins(:vacancies).where('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).

81034 次浏览
小开

是的，vacancies不是连接中的一个字段。我相信你想要:

Project.joins(:vacancies).group("projects.id").having("count(vacancies.id)>0")
小开
最佳答案

joins uses an inner join by default so using Project.joins(:vacancies) will in effect only return projects that have an associated vacancy.

更新:

正如@mackskatz 在评论中指出的，如果没有 group条款，上面的代码将返回有多个空缺的项目的重复项目。要删除副本，请使用

Project.joins(:vacancies).group('projects.id')

UPDATE:

正如@Tolsee 所指出的，您也可以使用 distinct。

Project.joins(:vacancies).distinct

举个例子

[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\""
小开

这个错误告诉你，空缺基本上不是项目中的一列。

This should work

Project.joins(:vacancies).where('COUNT(vacancies.project_id) > 0')
小开

1)获得至少1个空缺的项目:

Project.joins(:vacancies).group('projects.id')

2)获得多于一个空缺的项目:

Project.joins(:vacancies).group('projects.id').having('count(project_id) > 1')

3)或者，如果 Vacancy模型设置计数器缓存:

belongs_to :project, counter_cache: true

then this will work, too:

Project.where('vacancies_count > ?', 1)

vacancy的变形规律可能需要是 手动指定？

小开 
# 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')
小开

Without much Rails magic, you can do:

Project.where('(SELECT COUNT(*) FROM vacancies WHERE vacancies.project_id = projects.id) > 0')

这种类型的条件将在所有 Rails 版本中工作，因为大部分工作是直接在 DB 端完成的。另外，链接 .count方法也可以很好地工作。我以前被像 Project.joins(:vacancies)这样的查询搞得焦头烂额。当然，有利有弊，因为它不是数据库不可知的。

小开

在 Rails 4 + 中，你也可以使用 包括或者 立即装载来得到相同的答案:

Project.includes(:vacancies).references(:vacancies).
where.not(vacancies: {id: nil})


Project.eager_load(:vacancies).where.not(vacancies: {id: nil})
小开

我认为有一个更简单的解决办法:

Project.joins(:vacancies).distinct
小开

对 has _ many 表执行与 group或 uniq相结合的内部连接可能效率非常低，在 SQL 中，最好将其实现为使用 EXISTS和相关子查询的半连接。

这允许查询优化器探测空缺表，以检查是否存在具有正确 project _ id 的行。有一行还是一百万行具有 project _ id 并不重要。

这在 Rails 中并不简单，但是可以通过以下方式实现:

Project.where(Vacancies.where("vacancies.project_id = projects.id").exists)

同样，找到所有没有空缺的项目:

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 exists being delegated to arel. Fix this with:

Project.where.not(Vacancies.where("vacancies.project_id = projects.id").arel.exists)

编辑: 如果您对原始 SQL 感到不舒服，请尝试:

Project.where.not(Vacancies.where(Vacancy.arel_table[:project_id].eq(Project.arel_table[:id])).arel.exists)

通过添加类方法来隐藏 arel_table的使用，可以减少这种混乱，例如:

class Project
def self.id_column
arel_table[:id]
end
end

所以..。

Project.where.not(
Vacancies.where(
Vacancy.project_id_column.eq(Project.id_column)
).arel.exists
)
小开

您还可以将 EXISTS与 SELECT 1一起使用，而不是从 vacancies表中选择所有列:

Project.where("EXISTS(SELECT 1 from vacancies where projects.id = vacancies.project_id)")
小开

如果我想知道有多少条记录至少有一条相关记录，我会这样做:

Project.joins(:vacancies).uniq.count

提交答案