How to write query for a condition on join table

Question

I am trying to write a query using Rails ORM. Suppose there are two models.

class College

has_many :students

class Student

belongs_to :college

The Student table has a column named result which can either be pass or fail Now, I want to get the list of only those colleges where all students have passed.

I tried the following

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

But I am getting this error: Caused by PG::UndefinedColumn: ERROR: column "students_count" does not exist

Answer 1

Best way to do this is as a negative subquery:

College.where.not(id: Student.select(:college_id).where(result: "fail"))