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

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

Thank you!

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

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.

Is there a way to save a record skipping the counter_cache callback?

I'm uploading some pictures via Carrierwave and would like to trigger the reset_counters after all my batch uploading has finished.

You have to make sure in your model, you don't specify "counter_cache: true" when you're running your migrations. Otherwise, it throws "marked as read only" errors. It also does this when rolling back. So take note. I haven't been able to find a way around this. It seems you have to remove "counter_cache: true" when rolling back or migrating through a migration with counter_cache.

Wasted too much time just for a simple migration -_-

Also for rails 4, the syntax should be

Project.reset_column_information
Project.find_each.each do |p|
Project.update_attribute :tasks_count, p.tasks.length
end

update_counters, reset_counters, are all deprecated.
Note that the arguments syntax is different.

find_each is preferable because it executes in batches, not all at once.

I am getting tasks_count is marked as readonly while running migration. Fixed with solution given on SO

Check me if I'm wrong. Follow my understanding, in rails 4.2 it automatically uses this technique, is that right? https://github.com/rails/rails/issues/19042

to avoid the readonly error, use "reset_counters" instead of "update_attribute":

Project.reset_column_information
Project.all.find_each do |p|
  Project.reset_counters p.id, :tasks
end

This episode has been updated for Rails 5 as a blog post. Counter Cache Column in Rails 5