Rails - Mar 2006 - Should counter_cache fields be saved in the database?

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

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

On 3/24/06, Ryan Nielsen <rpnielsen@gmail.com>
wrote:
>
> 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
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060324/aaa068ee/attachment.html

On 3/24/06, Emin Hasanov <emin@hasanov.com> 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

Comments below.

Quoting Ryan Nielsen <rpnielsen@gmail.com>:
[snip]
> >> 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
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

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

On 3/25/06, Jeffrey L. Taylor <jeff.taylor@ieee.org> wrote:
> 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.

Comments in-line.

Quoting Ryan Nielsen <rpnielsen@gmail.com>:
> 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
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"]
> >> 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?
> 
>From my observations, not from completely digging thru the code, I am
not sure parent.children(:refresh) does anything anymore.

Jeffrey

On 3/30/06, Jeffrey L. Taylor <jeff.taylor@ieee.org> 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