IdolHands.com :: Days in the Life of an Alpha Geek
Using counter_cache with STI Models
I only recently discovered the counter_cache option for Rails relationships, and I'm finding it invaluable in speeding up performance (and tests) by reducing the number SQL queries and preventing the unnecessary instantiation of objects. It works by storing (caching) the number of objects in a relationship in the table for the associated class, incrementing or decrementing the number as needed.
There are a couple of things that you need to do, however, to ensure that your counts start off right and to make the whole thing work with STI-based models.
Methinks the Code Doth Query Too Much
First, let's explore the core benefit of counter caching. Here's a typical "before" case, supposing that User has_many :comments and Comment belongs_to :user:
>> User.last.comments.size
This results in the following three SQL queries:
User Load (0.4ms) SELECT * FROM `users` LIMIT 1
User Columns (2.9ms) SHOW FIELDS FROM `users`
SQL (0.3ms) SELECT count(*) AS count_all FROM `comments` WHERE (`pages`.user_id = 1)
We can cut that down to a single query by adding the following to the relationship in comment.rb:
belongs_to :user, :counter_cache => true
The User table also needs a new attribute:
add_column :users, :comments_count, :integer
Now, whenever a new Comment is created or destroyed, the comments_count for the appropriate User model will be automatically incremented. Now our previous query only requires one trip to the database, to fetch the value of this column:
User Load (0.4ms) SELECT * FROM `users` ORDER BY users.id DESC LIMIT 1
Nice! That resulted in a performance boost of .4ms over 3.6ms. These numbers are small, but that's still a 9x speed improvement.
Making This Work with STI Models
What iff you want to do the same thing with models that use single-table inheritance (STI)? This one stumped me for a while, so hopefully you will find this tip useful.
For a relationship involving an STI model, there's a slight modification that you need to put in place when you're defining the counter_cache.
Let's say that you have a base class of Page, subclassed into Article and Review, and that Articles belong to Topics. Since in the database Articles are stored in the pages table, you'll need to be more specific in your configuration of the counter_cache In articles.rb:
belongs_to :topic, :counter_cache => :articles_count
So Topic would get the column specified in the counter_cache argument:
add_column :topics, :articles_count, :integer
Now everything should work as expected.
Retrofitting Counter Cache
If you're coming to counter_cache late in the game—in other words, you already have models in your database with relationships—you might be a little surprised by the result of calling @model.related_models.count. In short, you'll find that the value only reflects related models that were created or destroyed after you put the counter_cache in place. For that reason, I recommend inserting the following to the migration you create for adding the counter column to your table.
Topic.all.each{|t| t[:articles_count] = t.articles.count; t.save}This will start you off with the correct counts. Just remember to update this with your own model names!
More Info
For the official scoop on counter_cache, refer to the Active Record Associations Class Methods page of the Rails API.
Related Articles
Comments
Randy
June 8, 2009 at 9:35 AMThis is exactly what I am going to have to do with a project soon. I am redoing a site, and will introduce a counter, so I will need to update the records to reflect whats currently there. Good article!
Tony Primerano
July 21, 2009 at 9:18 AMIn order for the migration to work you need to comment out counter_cache as it makes the counter read only. Then uncomment after migration is done. I spun my wheels on this one for a while. :-) Also, is counter_cache only for belongs_to? I see posts around where people use it on has_many but the docs don't mention that.
