Sat Apr 19 11:17:00 UTC 2008

I ran into a gotcha trying to use the :counter_cache option on a belongs_to association today. In short, the :counter_cache option can be used on the belongs_to end of a one-to-many association to improve the performance of querying the size of the association. As a quick example

class Dog < ActiveRecord::Base
  has_many :fleas
end

class Flea < ActiveRecord::Base
  belongs_to :dog
end

charlie = Dog.create(:name => "charlie")
charlie.fleas.create(:name => "fleabert")
charlie.reload
charlie.fleas.size

When this code is run from script/console, the development log will show you that in order to get the size of the fleas association on charlie, this sql was run

SELECT count(*) AS count_all FROM `fleas` WHERE (fleas.dog_id = 1)

If you use :counter_cache, however, you can save yourself that extra db query when trying to determine the size of an association.

The Rails Framework docs explain the usage of the option, and in its simple form, all you need to do is add :counter_cache => true to the belongs_to association. So in the above example,

class Flea < ActiveRecord::Base
  belongs_to :dog, :counter_cache => true
end

and add a column named #{table_name}_count to the associate class (so add fleas_count to dog, in this case) of type integer.

I did this and then reran the code where I create a dog and an associated flea, reload the dog, then ask for the size of the flea association. My development log was still showing a SELECT count(*) statement being executed.

I studied the log a bit more and found how ActiveRecord was trying to manage the counter cache. After creating the flea and adding it to the fleas association, this query is run

UPDATE dogs SET `fleas_count` = `fleas_count` + 1 WHERE (`id` = 1)

I checked the structure of the dogs table, and noticed that the default value for the fleas_count count column was NULL. Obviously trying to add 1 to NULL wasn’t going to be successful.

The answer, obviously, was to update my migration and specify a default value of 0 for the counter_cache column

t.integer :fleas_count, :default => 0

The counter cache then worked as advertised.

Tags: rails