[Dbix-class] Unnecessary re-fetching via belongs_to relationships?

[Matt S Trout]

Mark Blythe wrote:
> I have a situation where I see many seemingly needless queries
> happening.  This happens when I access a set of related child records
> via a has_many relationship, and then reference that original parent
> object via a belongs_to relationship from the children.  For each
> child object, the parent object is being re-fetched from the database,
> even though that child was originally fetched *through* the parent
> object.  For example (incomplete class examples):
> 
> package MyApp::Schema::Main::Parent;
> 
> __PACKAGE__->table('parent');
> __PACKAGE__->add_columns('parent_id');
> __PACKAGE__->set_primary_key('parent_id');
> 
> __PACKAGE__->has_many(children => 'MyApp::Schema::Main::Child', 'parent_id');
> 
> 
> package MyApp::Schema::Main::Child;
> 
>  __PACKAGE__->table('child');
>  __PACKAGE__->add_columns('child_id');
>  __PACKAGE__->set_primary_key('child_id');
> 
>  __PACKAGE__->belongs_to(parent => 'MyApp::Schema::Main::Parent', 'parent_id');
> 
> Now, somewhere else (via catalyst app):
> 
> my $parent = $c->model('Main::Parent')->find($parent_id);
> 
> foreach my $child ($parent->children()) {
>     print "child: ", $child->child_id, " parent: ",
> $child->parent->parent_id, "\n";
> }
> 
> So every time through the loop, $child->parent->parent_id causes the
> parent record to be re-fetched from the database (according to DBIC
> debug output).

Yeah, it currently does that. Making it the same object would either create a 
circular reference or result in one copy going out of scope taking the rest 
out of scope.

I think the best answer may be to have a caching mode where DBIC caches *all* 
lookups for data and the user has easy access to selectively invalidate parts 
of that cache - for example in a Catalyst app one would usually want to 
invalidate everything at the end of the request since we don't know how long 
it'll be until the next one.

-- 
      Matt S Trout       Offering custom development, consultancy and support
   Technical Director    contracts for Catalyst, DBIx::Class and BAST. Contact
Shadowcat Systems Ltd.  mst (at) shadowcatsystems.co.uk for more information

+ Help us build a better perl ORM: http://dbix-class.shadowcatsystems.co.uk/ +