# Should counter\_cache fields be saved in the database?

**URL:** <https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855>\
**Category:** Rails\
**Created:** [March 24, 2006, 10:02am UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855 "2006-03-24T10:02:17Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![ryana](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea5d25/32.png) [@ryana](https://www.ruby-forum.com/u/ryana)\
**Post date:** [March 24, 2006, 10:02am UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855/1 "2006-03-24T10:02:17Z")

</div>

As far as I can tell, the counter\_cache option on a belongs\_to model  
doesn’t actually save anything in the database. For example, I have  
the following models:

class Parent \< ActiveRecord::Base  
has\_many :children,  
:conditions =\> “deleted\_at IS NULL”  
end

class Child \< ActiveRecord::Base  
belongs\_to :parent, :counter\_cache =\> true  
end

With the following schema, dumped from sqlite3:

CREATE TABLE children (“id” INTEGER PRIMARY KEY NOT NULL NOT NULL,  
“name” varchar(255), “parent\_id” integer, “deleted\_at” datetime);  
CREATE TABLE parents (“id” INTEGER PRIMARY KEY NOT NULL NOT NULL,  
“children\_count” integer DEFAULT 0, “name” varchar(255));  
CREATE TABLE schema\_info (version integer);  
CREATE UNIQUE INDEX children\_id\_index ON children (id);  
CREATE UNIQUE INDEX parents\_id\_index ON parents (id);

I would expect the children\_count column in each Parent to be  
populated with the number of children as I create and delete  
associated children. However, that does not seem to be the case.

For example, I create a new parent and child in the console and verify  
the number of children. When I quit and restart the console, however,  
the children\_size column is 0.

[flashbang:…WebContent/rails/development/test] ryann% ./script/console  
Loading development environment.

> > parent = Parent.create  
> > =\> #\<Parent:0x2385f24 @new\_record\_before\_save=false,  
> > @new\_record=false, @errors=#\<ActiveRecord::Errors:0x2351dc8  
> > @errors={}, @base=#\<Parent:0x2385f24 …\>\>, @attributes={“name”=\>nil,  
> > “id”=\>1, “children\_count”=\>0}\>
> 
> > parent.children.create  
> > =\> #\<Child:0x234d070 @new\_record=false,  
> > @errors=#\<ActiveRecord::Errors:0x2349e48 @errors={},  
> > @base=#\<Child:0x234d070 …\>\>, @attributes={“name”=\>nil,  
> > “deleted\_at”=\>nil, “id”=\>1, “parent\_id”=\>1},  
> > @parent=#\<Parent:0x23461d0 @attributes={“name”=\>nil, “id”=\>“1”,  
> > “children\_count”=\>“0”}\>\>
> 
> > parent.children.size  
> > =\> 1
> 
> > parent.children\_count  
> > =\> 1
> 
> > Parent.find\_first  
> > =\> #\<Parent:0x233fee8 @attributes={“name”=\>nil, “id”=\>“1”,  
> > “children\_count”=\>“1”}\>
> 
> > parent.save  
> > =\> true
> 
> > quit  
> > [flashbang:…WebContent/rails/development/test] ryann% ./script/console  
> > Loading development environment.
> 
> > Parent.find\_first  
> > =\> #\<Parent:0x2353d30 @attributes={“name”=\>nil, “id”=\>“1”,  
> > “children\_count”=\>“0”}\>

That is not at all what I expected to happen. Rather, since I  
specified the counter\_cache option in the Child model, I expected to  
see children\_count be equal to 1 after restarting the console.

Why have a database column if nothing is to be stored there? The  
counter\_cache option seems exceedingly useless, otherwise. At the very  
least, it doesn’t make any sense to create the column if nothing is to  
be written there… Did I screw up something?

–  
Ryan

---

<div class="post-metadata">

**Author:** ![ryana](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea5d25/32.png) [@ryana](https://www.ruby-forum.com/u/ryana)\
**Post date:** [March 24, 2006, 8:47pm UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855/2 "2006-03-24T20:47:45Z")

</div>

obviously, column is there to write data to it, not just for the beauty,  
but  
clearly something is missed here

just to check, when you restart database console do you still have any  
children records? maybe they didn’t get saved for some reason hence the  
count is 0

---

<div class="post-metadata">

**Author:** ![ryana](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea5d25/32.png) [@ryana](https://www.ruby-forum.com/u/ryana)\
**Post date:** [March 24, 2006, 9:31pm UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855/3 "2006-03-24T21:31:10Z")

</div>

On 3/24/06, Emin H. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> obviously, column is there to write data to it, not just for the beauty, but  
> clearly something is missed here
> 
> just to check, when you restart database console do you still have any  
> children records? maybe they didn’t get saved for some reason hence the  
> count is 0

Yeah, I’m certain the records are being created. The counter\_cache  
column is simply not getting committed to the database. To double  
check, I recreated a new Rails 1.0 app with the models that I sent out  
in my first message. The server is running OS X 10.4.5 and the  
database is sqlite3, with the schema I sent out in the first mail.  
Here’s the console output from this new application:

> > Parent.find\_all.size  
> > =\> 0  
> > Child.find\_all.size  
> > =\> 0  
> > parent = Parent.create  
> > =\> #\<Parent:0x2350748 @new\_record\_before\_save=false,  
> > @new\_record=false, … “children\_count”=\>0}\>  
> > parent.children.create  
> > =\> #\<Child:0x2349538 @new\_record=false, …\>\>  
> > parent.children.create  
> > =\> #\<Child:0x233f6f0 @new\_record=false, …\>\>  
> > parent.save  
> > =\> true  
> > parent.children[0].save  
> > =\> true  
> > parent.children[1].save  
> > =\> true  
> > parent.children.size  
> > =\> 2  
> > parent.children\_count  
> > =\> 2  
> > parent.reload  
> > =\> #\<Parent:0x2350748 @new\_record\_before\_save=false,  
> > @new\_record=false, @children=nil, … “children\_count”=\>“0”}\>  
> > parent.children.size  
> > =\> 0  
> > parent.children\_count  
> > =\> 0  
> > Parent.find\_all.size  
> > =\> 1  
> > Child.find\_all.size  
> > =\> 2  
> > parent.children(:refresh).size  
> > =\> 2  
> > parent.children\_count  
> > =\> 2

Finally, when I quit the console session and dump the sqlite3 db, I  
get the following:

sqlite\> .dump  
BEGIN TRANSACTION;  
CREATE TABLE schema\_info (version integer);  
INSERT INTO “schema\_info” VALUES(1);  
CREATE TABLE parents (“id” INTEGER PRIMARY KEY NOT NULL NOT NULL,  
“children\_count” integer DEFAULT 0, “name” varchar(255));  
INSERT INTO “parents” VALUES(1, 0, NULL);  
CREATE TABLE children (“id” INTEGER PRIMARY KEY NOT NULL NOT NULL,  
“name” varchar(255), “parent\_id” integer, “deleted\_at” datetime);  
INSERT INTO “children” VALUES(1, NULL, 1, NULL);  
INSERT INTO “children” VALUES(2, NULL, 1, NULL);  
COMMIT;

The counter\_cache column is never written to the database, even though  
I saved all of the model objects! Either the column is just there for  
looks and is never used or I’m missing something _really_ obvious.

–  
Ryan

---

<div class="post-metadata">

**Author:** ![ryana](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea5d25/32.png) [@ryana](https://www.ruby-forum.com/u/ryana)\
**Post date:** [March 25, 2006, 4:20pm UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855/4 "2006-03-25T16:20:10Z")

</div>

Comments below.

Quoting Ryan N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):  
[snip]

> =\> #\<Child:0x233f6f0 @new\_record=false, …\>\>
> 
> > > parent.save

Here is the problem. The two creates updated the children\_count in  
the database, but not in memory. This is not how AWDR describes it,  
but it is my experience with RoR 1.0.0. So when you save the parent,  
you are overwriting the correct value in the database with the  
incorrect value (0) in memory. Move the parent save BEFORE the  
creation of the children.

HTH,  
Jeffrey

---

<div class="post-metadata">

**Author:** ![ryana](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea5d25/32.png) [@ryana](https://www.ruby-forum.com/u/ryana)\
**Post date:** [March 27, 2006, 4:32am UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855/5 "2006-03-27T04:32:38Z")

</div>

Based on the documentation, source, and my experience, you are correct.

increment\_ and decrement\_counter are used to maintain a counter cache  
and they update the counter. Those methods use update\_all to achieve  
their defined goal. update\_all makes raw sql calls across the database  
connection.

Ok, cool. Got it.

What doesn’t make **any** sense is that the children\_count isn’t saved  
correctly after I refresh the in memory object! I expect a save to,  
well, save the in memory object to the database. Example:

> > parent = Parent.create  
> > =\> #\<Parent:0x2385ed4 …\>
> 
> > parent.children.create  
> > =\> #Child:0x234d020...\>
> 
> > parent.children.create  
> > =\> #Child:0x23431d8...\>
> 
> > parent.reload  
> > =\> #\<Parent:0x2385ed4 … “children\_count”=\>“2”}\>
> 
> > parent.children\_count = 99  
> > =\> 99
> 
> > parent.save!  
> > =\> true
> 
> > parent.reload  
> > =\> #\<Parent:0x2385ed4 … “children\_count”=\>“99”}\>
> 
> > parent.children(:refresh)  
> > =\> [#\<Child:0x2328a54 …\>, #\<Child:0x2328a18 …\>]
> 
> > parent.children\_count  
> > =\> 2
> 
> > parent.save!  
> > =\> true
> 
> > parent.reload  
> > =\> #\<Parent:0x2385ed4 … “children\_count”=\>“99”}\>

Aaaah! I do _not_ expect the children\_count of parent to be 99 after  
refreshing it to the correct value and _saving_!

When I save an object, I expect the in memory changes to be commited  
to the database. So, when I change an obejct in memory (say, by  
refreshing its count\_cache) and call save, I expect to have an  
identical object when I immediately call reload. Looking at the source  
in TOT rails, every single attribute for a class sould be saved, no  
exceptions. What explains the disturbing behavior I’m seeing?

–  
Ryan

---

<div class="post-metadata">

**Author:** ![ryana](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea5d25/32.png) [@ryana](https://www.ruby-forum.com/u/ryana)\
**Post date:** [March 30, 2006, 4:53pm UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855/6 "2006-03-30T16:53:35Z")

</div>

Comments in-line.

Quoting Ryan N. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

> correctly after I refresh the in memory object! I expect a save to,
> 
> > > parent.children\_count = 99  
> > > =\> 99  
> > > parent.save!  
> > > =\> true  
> > > parent.reload  
> > > =\> #\<Parent:0x2385ed4 … “children\_count”=\>“99”}\>  
> > > parent.children(:refresh)  
> > > =\> [#\<Child:0x2328a54 …\>, #\<Child:0x2328a18 …\>]  
> > > parent.children\_count  
> > > =\> 2

This accessor method retrieves the value from the database, it does  
not return the in-memory value! Compare:  
parent.children\_count  
parent.@attributes[“children\_count”]

> refreshing its count\_cache) and call save, I expect to have an  
> identical object when I immediately call reload. Looking at the source  
> in TOT rails, every single attribute for a class sould be saved, no  
> exceptions. What explains the disturbing behavior I’m seeing?

> From my observations, not from completely digging thru the code, I am  
> not sure parent.children(:refresh) does anything anymore.

Jeffrey

---

<div class="post-metadata">

**Author:** ![ryana](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/r/ea5d25/32.png) [@ryana](https://www.ruby-forum.com/u/ryana)\
**Post date:** [April 4, 2006, 12:54am UTC](https://www.ruby-forum.com/t/should-counter-cache-fields-be-saved-in-the-database/55855/7 "2006-04-04T00:54:09Z")

</div>

On 3/30/06, Jeffrey L. Taylor [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> This accessor method retrieves the value from the database, it does  
> not return the in-memory value! Compare:  
> parent.children\_count  
> parent.@attributes[“children\_count”]

Ok. I really need to have a 1:1 with the code and work out what is  
cached and when the caches are referenced over the database.

> From my observations, not from completely digging thru the code, I am  
> not sure parent.children(:refresh) does anything anymore.

Well, I refactored a bunch of my code such that fewer children are  
created behind rails’ back. This solved a lot of problems.  
Unfortunately, I’m using the acts\_as\_paranoid plugin, so the  
count\_cache still get off when I “restore” a destroyed child by  
setting its deleted\_at column to nil. To fix that problem I have the  
following line in the Child model’s after\_update callback:

self.parent.update\_attributes({:children\_count =\>  
self.parent.children\_count(:refres  
h)})

That does the trick, so I’m pretty sure the (:refresh) flag still  
works. (In Rails 1.1, at least.)

–  
Ryan