Forum: Ruby on Rails Should counter_cache fields be saved in the database?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ac1feb66b7653d4e113cc3a31ae94e1d?d=identicon&s=25 Ryan Nielsen (Guest)
on 2006-03-24 10:02
(Received via mailing list)
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
7cda78d70ab775710f6fa24e0a5b68db?d=identicon&s=25 Emin Hasanov (Guest)
on 2006-03-24 20:47
(Received via mailing list)
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
Ac1feb66b7653d4e113cc3a31ae94e1d?d=identicon&s=25 Ryan Nielsen (Guest)
on 2006-03-24 21:31
(Received via mailing list)
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
Eb159164502bc1e2efb75ca7172805a0?d=identicon&s=25 Jeffrey L. Taylor (Guest)
on 2006-03-25 16:20
(Received via mailing list)
Comments below.

Quoting Ryan Nielsen <rpnielsen@gmail.com>:
[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
Ac1feb66b7653d4e113cc3a31ae94e1d?d=identicon&s=25 Ryan Nielsen (Guest)
on 2006-03-27 04:32
(Received via mailing list)
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
Eb159164502bc1e2efb75ca7172805a0?d=identicon&s=25 Jeffrey L. Taylor (Guest)
on 2006-03-30 16:53
(Received via mailing list)
Comments in-line.

Quoting Ryan Nielsen <rpnielsen@gmail.com>:
> 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
Ac1feb66b7653d4e113cc3a31ae94e1d?d=identicon&s=25 Ryan Nielsen (Guest)
on 2006-04-04 00:54
(Received via mailing list)
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
This topic is locked and can not be replied to.