查找关联计数大于零的所有记录 [英] Find all records which have a count of an association greater than zero

问题描述

我正在尝试做一些我认为很简单但似乎并不简单的事情.

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

我想获得至少有 1 个空缺的所有项目.我试过这样的事情:

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:没有这样的列:空缺:SELECT "projects".* FROM "projects" INNER JOIN "vacancies" ON "vacancies"."project_id" = "projects"."id" WHERE ("projects"."deleted_at" IS NULL) AND (count(vacancies) > 0).

推荐答案

joins 默认使用内部联接，因此使用 Project.joins(:vacancies) 将生效只返回有相关空缺的项目.

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 子句，上面的代码将返回具有多个空缺的项目的重复项目.要删除重复项，请使用

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

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

更新:

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

As pointed out by @Tolsee, you can also use 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""

这篇关于查找关联计数大于零的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！