CODEWITHSUNDEEP
ruby-on-railsactiverecord
pixelearth
pixelearth

Reputation: 14630

how can I include association.count in ActiveRecord query so that it doesn't perform a second query?

In my models I frequently have has_many associations. I also frequently need to display how many there are (i.e. assoc.count). This forces another query to be performed, as demonstrated below:

ruby-1.8.7-p334 :020 > Instructor.first.instructors_lessons.count
  Instructor Load (0.5ms)  SELECT `users`.* FROM `users` INNER JOIN `instructor_profiles` ON `instructor_profiles`.`instructor_id` = `users`.`id` LIMIT 1
  SQL (1.2ms)  SELECT COUNT(*) FROM `instructors_lessons` WHERE (`instructors_lessons`.instructor_id = 2817)

This is fine when there is one or two, but when there is 100+, this process gets noticeably slow. Please see this egregiously slow process (http://pastebin.com/p4Sj7q7s). I've been working around one very slow page with caching.

I can put this in the query like so: but this is tedious and kind of defeats the purpose of ActiveRecord associations I feel:

 select("instructors.*, count('instructors_lessons.instructor_id') as num_lessons").

But then I can't simply say instructor.lessons.count...

Is there a way to use joins() or includes() so that this extra COUNT() query doesn't need to be performed?

Upvotes: 1

Views: 1465

Answers (1)

Gazler
Gazler

Reputation: 84180

Sounds like you are looking for a counter cache.

You simply create a column with _count and then specify counter_cache in your belongs to association.

In your case you would add a field called "lessons_count" to your instructor column. Then do the following:

belongs_to :instructor, :counter_cache => true

This value will automatically update the total when the total associated records changes.

Upvotes: 7

Related Questions