Railscasts - Counter Cache Column

#23

Apr 25, 2007

Counter Cache Column

If you need to display the record count for a has_many association, you can improve performance by caching that number in a column.

Tags: performance active-record

Download (18.8 MB, 7:18)

alternative download for iPod & Apple TV (10.7 MB, 7:18)

Update (3/29/08)

The migration code shown in this screencast does not work in Rails 2.0. Instead please use the code below. Special thanks to Josh Owens for this blog post describing the problem and solution.

# migrations/006_add_tasks_count.rb
def self.up
  add_column :projects, :tasks_count, :integer, :default => 0

  Project.reset_column_information
  Project.find(:all).each do |p|
    Project.update_counters p.id, :tasks_count => p.tasks.length
  end
end

def self.down
  remove_column :projects, :tasks_count
end

# models/task.rb
belongs_to :project, :counter_cache => true

<!-- projects/index.rhtml -->
<%= pluralize project.tasks.size, 'task' %>

You can also find the original code here which should work for Rails versions before 2.0.

< Previous Episode

Next Episode >

30 comments

weskycn Apr 25, 2007 at 00:36

very good
我永远顶你！！

versus Apr 25, 2007 at 06:43

Nice!

Доброго вам здоровья

jgeiger Apr 25, 2007 at 07:20

Nice job. I really liked the migration part.

Sintaxi Apr 25, 2007 at 10:46

This is interesting. I probably never would have done this think it was redundant data. Thanks once again.

David Parker Apr 25, 2007 at 11:52

Another great screencast!

Ideas:
1) Setting up a Rails project using SVN (only a little off topic)
2) Deploying a Rails project using Capistrano

Keep it up Ryan!

peanut Apr 25, 2007 at 12:05

Thank you! This screencast is very interesting and useful!

Rebort Apr 25, 2007 at 13:54

Great stuff -- I had no idea you could do that, and I learned some things about migrations too.

Thank you!

ryan Apr 26, 2007 at 22:12

I would like to see acts_as_nested_set tutorial sometime. I'm currently doing an acts_as_nested_set with a self-referential, and it's not going too smooth. Love the screencasts - keep it up!

Blinchik Apr 27, 2007 at 00:16

thanks!
будьте здоровы :)

kerek Apr 27, 2007 at 01:38

I love the screencasts and the site! It has become first thing I check during my daily reading session. :)

I'm alse currently interested in act_as_nested_set - like ryan.

Grant Neufeld Apr 27, 2007 at 08:13

@David Parker: Railscasts’ sponsor, PeepCode, has a detailed screencast tutorial on Capistrano (with a free update to the version 2 screencast when that comes out):
http://www.peepcode.com/products/capistrano-concepts

David Parker Apr 27, 2007 at 08:24

Thanks Grant!... didn't check out that one yet. I ordered the RESTful one and I love it. I'll definitely look into it.

taco Apr 29, 2007 at 19:11

Thanks for the screencasts. They're great.

I am wondering why this approach is better than simply using length instead of size?

jaffet Jul 16, 2007 at 16:32

I never got this to work. I can see in the console, that the tasks_count has value when adding new tasks, but when finding all projects (with tasks) using project.tasks.size -method, I still see the "SELECT count(*) AS count_all FROM..." with as many ids as the projects. I have the counter_cache => true value in model. This is strange? (Is this only working in production -state?)

Ryan Bates Jul 17, 2007 at 10:50

@jaffet, not sure what the problem is. It should work in development mode which is the mode I was in for this screencast. What version of Rails are you using?

Jaffet Jul 20, 2007 at 06:42

Im using version 1.2.3

RainChen Jul 29, 2007 at 10:11

This is not working with the has_and_belongs_to_many relation.
If there are something like:

class Post < ActiveRecord::Base
has_and_belongs_to_many :tags, :counter_cache => true
end

will get an "ArgumentError" error:
Unknown key(s): counter_cache

how to resolve this situation?

Ryan Bates Jul 29, 2007 at 22:16

@RainChen, I don't think Rails provides an easy way to do this with the has_and_belongs_to_many association. You may want to consider using has_many :through instead.

Alternatively you can try building your own custom counter cache by using the after_add and after_remove callbacks.

RainChen Aug 03, 2007 at 03:24

I trying the "after_add and after_remove" way.But I met a problem.
I can successfully update the counter cache when adding or destroying the associated record.But how about the updating?
Any tips,thx?

JonTec Aug 20, 2007 at 13:15

@taco: #length does an SQL query with AR, while #size uses the version in memory and therefore the cached number

Andy Lai Sep 26, 2007 at 13:10

Is it possible to maintain counters of complete and incomplete tasks?

Basically I need counters for each state in my child model, and this approach works for counting # of children right?

Thanks

Ryan Bates Sep 28, 2007 at 14:15

@Andy, I think you'll need to keep track of this manually instead of using the counter_cache option. You'll have to set up an observer or callback to update the counter cache every time you change a task. You may want to ask this on railsforum.com where I can go into more detail.

Kalle Oct 24, 2007 at 07:37

Hi. Another great screencast! Thank you very much!

I'd like to do like a couple of other viewers and announce my interest in a cast about act_as_nested_set.

Thanks again...

Beate Oct 31, 2007 at 02:34

I think it's not possible any more in rails edge (2.0) to fill the count-colums afterwards, because (from the edge api):

"Note: Specifying a counter_cache will add it to that model’s list of readonly attributes using attr_readonly."

Some other idea for this?

Josh Owens Nov 02, 2007 at 23:35

Beate,

I recently posted about this very issue on my blog. I ran into the same problem about a week ago.

Check it out:http://josh.the-owens.com/archives/2007/11/03/rails-edge-change-how-to-add-a-counter-cache-to-an-existing-db-table/

Josh Owens Nov 02, 2007 at 23:36

"Counter Cache migration article":http://josh.the-owens.com/archives/2007/11/03/rails-edge-change-how-to-add-a-counter-cache-to-an-existing-db-table/

Beate Nov 05, 2007 at 00:24

Thanks Josh! It worked :)

kino May 23, 2008 at 01:55

Since knowledge of our ideas is a posteriori, the paralogisms of human reason, consequently, are by their very nature contradictory.

Jason May 29, 2008 at 09:57

I would also like to see a railscast on acts_as_nested_set (maybe even combined with some drag and drop sorting!)

руби форум Jul 20, 2008 at 07:11

thank it helped me

Counter Cache Column

Update (3/29/08)

30 comments

Add your comment: