Nov
3
A counter cache is a drop dead simple feature that makes counting child objects much faster. It allows you to keep a database field on the parent object, with the integer in it. Rails will update the counter for you when an association is made between the parent and child objects. This allows you to do a simple SQL query for the counter stored on the parent object, rather than counting *all* the associated objects each time. Very handy, but something changed in edge (2.0) recently that made all the instructions on tutorial sites stop working.
Counter cache has become a attr_readonly attribute, and as such you can’t directly mess with it and have the changes saved when you call object.save. Take this railscast example and give it a shot against the latest edge build… You will end up with all 0’s in the count column. So what is a guy/gal supposed to do to get some Counter Cache action into a db table for objects that are already in production? Let me show you my edge based migration…
def self.up
add_column :recipes, :saved_recipes_count, :integer, :default => 0
Recipe.reset_column_information
Recipe.find(:all).each do |r|
Recipe.update_counters r.id, :saved_recipes_count => r.saved_recipes.count
end
end
So if you note, we are using update_counters instead of update_attributes. The update_counters call takes two arguments, the first arg is the id of the object and the second arg is a hash of counters with the amount to increment by (or decrement if the number is negative). The update_counters call is the same thing rails uses behind the scene to update your counters when you add/remove child objects.
Hopefully someone will find this stuff helpful, it drove me nuts for about a half hour the other night.
Comments
6 Responses to “Rails Edge Change: How to add a counter cache to an existing db table”
Leave a Reply
Unfortunately it doesnt work when we want to repair counter cache column with NULL value. Only pure sql can help us.
[...] Josh Owens ยป Rails Edge Change: How to add a counter cache to an existing db table Counter cache attributes are protected in rails 2.0 so you need to use slightly different syntax to update them manually/in a migration (tags: countercache rails2.0 rubyonrails) [...]
As I posted on my site based on your comment, the time it takes to apply the migration is linear with the size of the table. If you have millions of recipes, the migration might take hours to apply. The query on my blog does everything with a single statement which will run MUCH MUCH faster for tables with large amounts of data:
http://www.mikeperham.com/2007/12/17/creating-a-counter_cache-column/
Thank you so much. As a Rails newbie this wasn’t the easiest nut to crack - until I saw this post.
This one drove me F*%&ing nuts for 2 hours. Thanks for the post! Seriously… this information doesnt seem to be in any book (Rails Way included!)
I just initialized a counter for a table with ActiveRecord… 20secs to run (and as Mike pointed out: would only increase as the rows count grows)
SAME operation with MyModel.connection.execute… 0.12 seconds - just over ONE TENTH of a second.
I think I’ll take the later.