CODEWITHSUNDEEP
postgresqlactiverecordruby-on-rails-5rails-activerecordhas-many-through
Philip7899
Philip7899

Reputation: 4677

Cannot use group with has_many through in Rails 5

I have the following associations:

class Student < ApplicationRecord
    has_many :people_schools
    has_many :schools, through: :people_schools
end

class PeopleSchool < ApplicationRecord
    belongs_to :student
    belongs_to :school
end

class School < ApplicationRecord
    has_many :people_schools
    has_many :students, through: :people_schools
end

I am trying to get a list of students organized by their school. I have tried the following:

Student.joins(:schools).all.group('schools.name')

but I get the following error:

ActiveRecord::StatementInvalid: PG::GroupingError: ERROR:  column "students.id" must appear in the GROUP BY clause or be used in an aggregate function

How do I fix this?

Upvotes: 0

Views: 754

Answers (1)

ulferts
ulferts

Reputation: 2242

When the association fires, it will generate a SQL query like

SELECT students.id, students. ...
FROM students
JOIN schools
ON ...
GROUP BY schools.name

In sql when grouping, a projection (SELECT) can only include columns that are grouped by or aggregations (e.g. MAX, MIN) of columns (regardless of whether they are grouped by). Therefore adding something like the following would turn it into a valid sql: 

# column that is grouped by
Student.joins(:schools).group('schools.name').select('schools.name')
# aggregate function
Student.joins(:schools).group('schools.name').select('schools.name, COUNT(students.id)')

But how you want to fix it in your case depends on what you want to get out of the query.

In answer to the comment

Assuming a student is only member of a single school, which requires changing the association to a belongs_to :school (without the join table) or a has_one :school (with join table).

Student.includes(:school).group_by(&:school)

This will issue an SQL statement to get all students and their school (eager loaded for optimization). Only after the models (Student, School) are instantiated ruby objects, is the group_by method evaluated, which will return a Hash where the school is the key referencing an Array of students.

Upvotes: 2

Related Questions