查找所有关联计数大于零的记录

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:

我想要所有至少有一个空缺的项目。我试过如下方法:

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:没有此类列:职位空缺:选择“项目”。*从“项目”内部加入“职位空缺”。project_id”=“项目”。“id”,(“项目”。“deleted_at”为空，(count() >)为空。

6 个解决方案

#1

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

join默认使用内部连接，因此使用project .join(:之二)实际上只返回具有相关空缺的项目。

UPDATE:

更新:

As pointed out by @mackskatz in the comment, without a group clause, 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')

#2

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 Vacancy model sets counter cache:

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

belongs_to :project, counter_cache: true

then this will work, too:

那么这也会起作用:

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

Inflection rule for vacancy may need to be specified manually?

空缺的变化规则可能需要手动指定?

#3

Yeah, vacancies is not a field in the join. I believe you want:

是的，职位空缺不是加入的领域。我相信你想要的:

Project.joins(:vacancies).group("projects.id").having("count(vacancies.id)>0")

#4

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

#5

-1

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 .count method 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端完成。另外，chaining .count方法也可以很好地工作。我以前遇到过像project .join (: empty)这样的查询。当然，这是有利有弊的，因为它不是数据库无关的。

#6

-4

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

#1