Rails - eager load the number of associated records, but not the record themselves.

Question

I have a page that's taking ages to render out. Half of the time (3 seconds) is spent on a .find call which has a bunch of eager-loaded associations. All i actually need is the number of associated records in each case, to display in a table: i don't need the actual records themselves. Is there a way to just eager load the count? Here's a simplified example:

@subjects = Subject.find(:all, :include => [:questions])

In my table, for each row (ie each subject) i just show the values of the subject fields and the number of associated questions for each subject. Can i optimise the above find call to suit these requirements?

I thought about using a group field but my full call has a few different associations included, with some second-order associations, so i don't think group by will work.

@subjects = Subject.find(:all, :include => [{:questions => :tags}, {:quizzes => :tags}], :order => "subjects.name")

:tags in this case is a second-order association, via taggings. Here's my associations in case it's not clear what's going on.

Subject
  has_many :questions
  has_many :quizzes

Question
  belongs_to :subject
  has_many :taggings
  has_many :tags, :through => :taggings

Quiz
  belongs_to :subject
  has_many :taggings
  has_many :tags, :through => :taggings

Grateful for any advice - max

Answer 1

You could implement a counter cache for this purpose. I maintain a counter on an Albums model that keeps track of how many Photographs are associated with it. My process looks somewhat like this (I believe I found this method on a blog somewhere, so I take no credit for the original code):

On the Photographs model:

after_save :update_counter_caches
after_destroy :update_counter_caches

def update_counter_caches
  self.albums.each { |a| a.update_count } unless self.albums.empty?
end

On the Albums model:

def update_count
  update_attribute(:photographs_count, self.photographs.length)
end

The migration you'd need on the Albums:

class AddCounterCacheColumnToModels < ActiveRecord::Migration
  def self.up
    add_column :albums, :photographs_count, :integer, :default => 0
  end

  def self.down
    remove_column :albums, :photographs_count
  end
end

Unless I've misunderstood your question, that should be a pretty neat way of achieving what you want. It works well in my current project. :)

---

EDIT: As a note, the reason I use this setup rather than the default :counter_cache method is because I need to maintain multiple counters for multiple associations on one model. As far as I know, you can't achieve that with :counter_cache.

Answer 2

the fastest way I think:

@subjects = Subject.count(:joins => :questions, :select => 'DISTINCT(questions.id)')

And I am not sure for more complex query (not tested):

@subjects = Subject.count(:joins => [{:questions => :tags}, {:quizzes => :tags}], :select => 'DISTINCT(tags.id)'

Answer 3

I believe the best is using :counter_cache on belongs_to association.

class Subject < ActiveRecord::Base
  has_many :questions
end

class Question < ActiveRecord::Base
  belongs_to :subject, :counter_cache => true
end

To use counter_cache you'll also need to add a questions_count column to your subjects table.

From railsapi.com:

:counter_cache
Caches the number of belonging objects on the associate class through the use of increment_counter and decrement_counter. The counter cache is incremented when an object of this class is created and decremented when it’s destroyed [...]