This project is archived and is in readonly mode.

Add add_counter_cache_column to ActiveRecord migrations

#228

This method makes it straightforward to add new columns to work with :counter_cache on ActiveRecord models. Apart from adding the column (default 0), it will update the counters for all the records.

For example:

def self.up
  add_counter_cache_column :posts, :comments
end

Creates a new comments_count column on posts and updates all current posts with the comments count -- equivalent to:

Post.reset_column_information
Post.find(:all).each do |post|
  Post.update_counters post.id, :comments_count => post.comments.count
end

Updated docs and test included.

Reported by Damian Janowski · July 17th, 2008 @ 01:52 AM

Activity

Ryan Bates
Nice. Would it fit to have a "t.counter_cache" method for use in create_table and change_table as well?

May 20th, 2008 @ 10:00 PM
Lawrence Pit
+1 for adding t.counter_cache.
About the loop: if there are millions of rows to be updated then the proposed code is too slow. The counter cache column can be updated with 1 UPDATE statement: UPDATE posts SET comments_count = (SELECT COUNT(*) FROM comments WHERE comment.post_id = post.id)

May 20th, 2008 @ 10:52 PM
Guillermo Álvarez
I think it could be cleaner something like these
t.references :post, :couter=> true
like we already do with for example null or polymorphic

May 20th, 2008 @ 11:29 PM
Ryan Bates
@Guillermo, interesting idea, but then how would one add the cache column if the post_id column already exists in comments table?
Since caching usually happens after analyzing performance, I think it is better kept as a separate step.

May 21st, 2008 @ 12:19 AM
Guillermo Álvarez
@Ryan_Bates with something like these
change_table :users do |u|
u.add_couter :posts
end
So you don't have to learn more methods name, because we will use the same as we already have (Well, t.add_counter is not already implemented). I think its more logical at these manner.

May 21st, 2008 @ 07:44 AM
Damian Janowski
@Lawrence Pit: that's a good idea, but it doesn't work if I have non-standard associations (different foreign keys or table names). I guess edge cases would have to be done by hand. However, I'll update the patch to do :select => klass.primary_key for some performance optimization.

May 21st, 2008 @ 05:42 PM
Damian Janowski
@Ryan Bates: adding the method to create_table and change_table is not so simple. Those take a TableDefinition, which is just that, a list of columns to be created after the block is called. So we'd need to implement a simple callback mechanism in order to be able to run the code that populates the counters...

May 21st, 2008 @ 05:53 PM
josh
- State changed from new to open
- Assigned user set to josh
- Tag set to activerecord, migrations
July 17th, 2008 @ 01:54 AM
Damian Janowski
Josh, thoughts?

July 17th, 2008 @ 02:54 PM
josh
After scanning over this ticket, I thought it was totally unnecessary at first. However, I really like the update counters migration that brings your counters update to date (nice touch).
Like Ryan said, you usually think about this stuff later so "t.counter_cache" isn't really needed.
Is this the final API you've decided on?
```
add_counter_cache_column :posts, :comments
```
July 19th, 2008 @ 01:42 AM
josh
Talked with some other peeps. Not really in favor of yet another migration helper.
However, I still like the cache resync helper. Maybe
```
# Reload comment counter cache
post.comments.count(reload = true)
```
July 19th, 2008 @ 06:47 PM
Ryan Bates
- Tag changed from activerecord, migrations to activerecord, migrations, patch
I like the look of the reload counter cache option, but I wonder if "count" is the right method for this. To me it makes more sense to use "size" since that is what normally uses the cache column. You could pass true to force the reload.
```
post.comments.size(true)
```
This way it parallels passing true to associations to reload them.
Maybe this deserves its own ticket?
July 21st, 2008 @ 11:53 PM
Pratik
- State changed from open to wontfix
Yeah. I think it makes sense to create a new ticket. I'd personally prefer something like post.comments.reset_counter_cache I think.

July 22nd, 2008 @ 01:19 AM
Trevor Turk
Sorry, I see a discussion here:

http://groups.google.com/group/r...

...but I can't find a new ticket about this issue. I've been bitten by this one before - it's very annoying!

October 14th, 2008 @ 07:41 PM
Trevor Turk

See new ticket here:

http://rails.lighthouseapp.com/p...

October 14th, 2008 @ 10:20 PM
Ryan Bigg
- Tag cleared.
- Importance changed from to Low
Automatic cleanup of spam.

October 9th, 2010 @ 10:14 PM
Ryan Bigg
Automatic cleanup of spam.

October 11th, 2010 @ 12:11 PM