如何用Rails ORM查询所有学生均通过的学院？解决SQL报错问题

模型定义 #

class College
  has_many :students
end

class Student
  belongs_to :college
end

需求说明 #

Student 表包含result字段，取值为pass或fail，需要查询出所有学生的 result 均为 pass的学院列表。

尝试的代码及报错 #

尝试的查询代码：

College.join(:students).group('colleges.id, students.results').select('colleges.id, count(students) as students_count').where(students: {result: 'pass'}).having('count(*) = students_count')

执行后报错：

Caused by PG::UndefinedColumn: ERROR:  column "students_count" does not exist

问题分析与解决方案 #

报错原因 #

SQL执行顺序中，HAVING子句的执行早于SELECT，因此SELECT里定义的别名students_count在HAVING中无法被识别，这是报错的直接原因。

除此之外，原查询逻辑也存在问题：按students.results分组会将同一学院下不同结果的学生拆分统计，且仅筛选pass的学生，会直接排除存在挂科学生的学院，无法准确筛选出所有学生均通过的学院。

正确方案 #

方案一：子查询排除法（简洁高效）

先找出所有存在挂科学生的学院ID，再排除这些学院，剩下的就是符合要求的学院（包含没有学生的学院，若不需要可自行调整）：

# 获取存在挂科学生的学院ID集合
failed_college_ids = Student.where(result: 'fail').distinct.pluck(:college_id)
# 排除上述学院，得到所有学生均通过的学院
College.where.not(id: failed_college_ids)

方案二：分组统计法（适合需要查看统计数据的场景）

通过统计每个学院的总学生数与通过学生数，当两者相等时即为符合条件的学院：

College.left_joins(:students)
       .group('colleges.id')
       .select('colleges.*, COUNT(students.id) AS total_students, SUM(CASE WHEN students.result = \'pass\' THEN 1 ELSE 0 END) AS passed_students')
       .having('COUNT(students.id) = SUM(CASE WHEN students.result = \'pass\' THEN 1 ELSE 0 END) OR COUNT(students.id) = 0')

• 使用left_joins是为了包含没有学生的学院，若不需要这类学院，可替换为joins，同时去掉OR COUNT(students.id) = 0条件。

内容的提问来源于stack exchange，提问作者Ravi