Should counter_cache fields be saved in the database?

Rails
1

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

2

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

3

On 3/24/06, Emin H. [email protected] 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

4

Comments below.

Quoting Ryan N. [email protected]:
[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

5

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

6

Comments in-line.

Quoting Ryan N. [email protected]:

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

7

On 3/30/06, Jeffrey L. Taylor [email protected] 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