#23 Counter Cache Column

Nice job. I really liked the migration part.

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

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!

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

Thank you!

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!

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.

@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

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

Thanks for the screencasts. They're great.

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

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?)

@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?

Im using version 1.2.3

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?

@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.

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?

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

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

@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.

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...

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?

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/

"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/

Thanks Josh! It worked :)

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

Interesting railscast. Does it work out of the box with pagination plugins, like will_paginate ?

How to add counter_cache if I have has_many_polymorphs?
For example:

I have model Topic and Articles:

in Topic

  has_many_polymorphs :affiliateds,
    :from => [:topics, :articles],
    :through => :affiliations,
    :dependent => :destroy,
    :as => :affiliate

How to add topics_count, articles_count?

Thanks for the information about how to add a counter cache to an existing db
However I found that solution a little bit to complex for me. I migrate tables where some inital row values in the db are generated using Model.create in the migration class, which cannot be done with counter caching.
Instead I just made a rake file with the same Model.create calls and that seems to work, if I just generate the values in a correct order.
Any comments on that solution?

Thanks for the great tips Ryan. I originally saw this RailsCast a while back, but came back to it today for some info. I just want to say again that you are an amazing part of the Rails community.

Ryan, do you have any experience or advice on working with counter_caches that also have conditions on them?

For instance, what is the best way, or a very good way to go about handling a situation where you want to count only unread messages and also count the total number of messages with a counter_cache? for the total number of messages it's easy. but the unread have some conditions that would obviously need to be checked.

I've had nothing but problems implementing counter_cache in an ajaxy application. The changes are simply not propagated to the in-memory object as expected.

I have ended up going with a hybrid solution, where I use the counter cache only on pages where the count value cannot change (such as an index of articles with comment counts, explicitly using article.comments_count ). On pages where the count can change, such as an article detail page with a new comment form and an ajaxy updated comments block, I use article.comments.length.

Thanks to this comment: 23. Beate Oct 31, 2007 at 02:34

I could look into my error. The link to Josh Owens' Website is not anymore working.

Here's the simplest solution. The code in the migration *will* work, but before you set ":counter_cache => true" in the model.

That said, I'd love to see the code in this page (and in pastie) updated with the "other solution".

Thanks for your good work Ryan. Cheers.

Here's the Evil Hack that temporarily makes your counter cache attribute non-readonly so that you can initialize it properly during up migration - http://blog.bitfluent.com/post/30194408/updating-counter-cache-in-migrations

I was having trouble getting this to work (on Rails 3). This helped http://millarian.com/programming/ruby-on-rails/rails-counter_cache-problem/

Using the migration (as amended 03/29/08 - blog no longer available) on a large database took a very long time.

Actually, using tasks.count in Rails 2.3 works so:

Project.reset_column_information
    Project.find(:all).each do |p|
      b.update_attribute :tasks_count, p.tasks.count
end

Is now fine and runs fast enough on a reasonable database size. But make sure this is run before setting counter_cache in the model.
Because once set, tasks_count will become read only and won't update.

I am guessing that Rails was modified so that using count always references the tasks table and to use the cached value you directly read tasks_count. But there seems no reference to this in the API.

But for very large databases, an sql updater to set this column would probably be better.

For those looking for has_many :through counter cache, here's a great post on stackoverflow.

In Rails3.0.5 you are now able to set counter cache to the "linker" model, in your case it will be:

class Meeting_user < ActiveRecord::Base
belongs_to :meeting, :counter_cache => "users_count"
belongs_to :user, :counter_cache => "meetings_count"

http://stackoverflow.com/questions/4700247/counter-cache-has-many-through-sql-optimisation-reduce-number-of-sql-queries

For anyone still referencing this, current ActiveRecord versions now have a reset_counters convenience class method that will automatically execute a count query for you. Also, please do use find_each instead of find(:all).each :-)

I'm not wild about using migrations to modify data personally, but if you insist:

def self.up
  add_column :projects, :tasks_count, :integer, :default => 0

  Project.reset_column_information
  Project.select(:id).find_each do |p|
    Project.reset_counters p.id, :tasks
  end
end

def self.down
  remove_column :projects, :tasks_count
end

For anyone still supporting a rails 2.3x app, the syntax that works for your migration is

def self.up
add_column :users, :tasks_count, :integer, :default => 0

User.reset_column_information
User.find(:all).each do |u|
 User.update_counters u.id, :tasks_count => u.tasks.count
end

end

def self.down
remove_column :users, :tasks_count
end

note the handling of tasks_count in the do loop...

We ran into a lot of issues with Rails counter-caches in our app, so we made a gem to solve all of them. Maybe it'll help you as well? Would love feedback, too.
https://github.com/bestvendor/counter_culture/

can I use this with has_and_belongs_to_many

I tried "counter_culture" gem and it is working great. Thank you.

When I recently tried implementing this, I landed up with the error "attribute" is marked as readonly . Here, the "attribute" refers to the counter cache attribute. To fix the same, this post helped me.

FYI, to those that have :inheritance_column set to something other than :type on the class you are calling :reset_column_information, make sure you set it again after the :reset_column_information call or it will give you the "column 'type' is reserved" error when running the migration.

This is because the class :inheritance_column setting is effectively ignored after you call :reset_column_information after the class is loaded. So, in short, assuming the Project class had :inheritance_column set to something other than :type, your migration should be:

Project.reset_column_information
Project.inheritance_column = nil #or whatever you have it set to
  Project.find(:all).each do |p|
    Project.update_counters p.id, :tasks_count => p.tasks.length
  end

Took a while for me to realize what was going on, so hopefully this saves someone some time.