CODEWITHSUNDEEP
ruby-on-railsactiverecord
rigyt
rigyt

Reputation: 2353

Includes not allowing outer join and count on child table

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.

Upvotes: 4

Views: 317

Answers (1)

Paritosh Piplewar
Paritosh Piplewar

Reputation: 8122

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')

Upvotes: 4

Related Questions