Includes not allowing outer join and count on child table

Question

class Course < ActiveRecord::Base
  has_many :registrations

  delegate :count, to: :registrations, prefix: true
end

class Registration < ActiveRecord::Base
  belongs_to :course
end

In my Courses index view I show a count of Registrations for each record. What is best practice for this? Using includes(:registrations) with the delegated registrations_count method looks slower (in the console) than doing a database count in the main query.

I want to show all records, so I can't use an INNER join(). Using includes() as below gives the error PG::GroupingError: ERROR: column "registrations.id" must appear in the GROUP BY clause or be used in an aggregate function I tried adding a where clause on :registrations to this but it still errored:

Course.includes(:registrations).group("courses.id").select("courses.*, COUNT(registrations.id) as registrations_count")

Is it right to have to specify the outer join as follows?

Course.joins('LEFT OUTER JOIN registrations ON registrations.course_id = courses.id').select('courses.*, COUNT(registrations.id) as registrations_count').group('courses.id')

This last query does work but it feels what I'm doing should be fairly standard so I'd like to be sure I'm taking the right approach in general.

score 4 · Answer 1 · edited May 23 '17 at 12:11

In my Courses index view I show a count of Registrations for each record. What is best practice for this?

counter_caching is the best option for counting association objects. A counter cache in Rails is just an additional column that tracks the number of associated models.

You just have to do this

belongs_to :course , counter_cache: true

And then you can simply do this

@course.registrations_count

Learn more in RailsCast

Looks like you are missing the difference between includes vs joins, Learn here

For me this is good.

Course.joins('LEFT OUTER JOIN registrations ON registrations.course_id = courses.id').select('courses.*, COUNT(registrations.id) as registrations_count').group('courses.id')

I know the includes v joins difference just surprised the left outer join needs to be written explicitly here. But not if using counter_cache :) thanks. Looks like it would be accessed via a @course.registrations_count column though, not registrations.size? — rigyt, Apr 29 '14 at 12:42

Includes not allowing outer join and count on child table

1 Answers1