[Dbix-class] Avoid DB hit with many_to_many rel

[Moritz Onken]

Hi,

I have a table 'categories' which has a many_to_many relationship to  
itself using a table 'map_categories'.

I found out that prefetching the many_to_many rel does not work. So I  
prefetched the has_many rel to 'map_categories' (parents) and prefetch  
the belongs_to rel (parent) from 'map_categories' to 'categories' ($rs- 
 >search({},{prefetch => {'parents' => 'parent'}})->all). This does  
the sql statement I expect but when I call $row->parents->all (or $row- 
 >parents->first->parent) on a row of this rs it hits the database  
again. Although the resultset already contains all rows it needs.

Any ideas what I did wrong?

cheers,

moritz

package Schema::Categories;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("categories");
__PACKAGE__->add_columns(
   "title",
   {
     data_type => "character varying",
     default_value => undef,
     is_nullable => 1,
     size => 100,
   },
   "id",
   {
     data_type => "integer",
     is_nullable => 0
   });

__PACKAGE__->set_primary_key("id");

__PACKAGE__->has_many("parents" => 'MapCategories' => "child");

#__PACKAGE__->many_to_many(many_parents => "parents" => "parent");

1;

package Schema::MapCategories;

use strict;
use warnings;

use base 'DBIx::Class';

__PACKAGE__->load_components("PK::Auto", "Core");
__PACKAGE__->table("map_categories");
__PACKAGE__->add_columns(
   "child",
   {
     data_type => "integer",
     is_nullable => 0,
   },
   "parent",
   {
     data_type => "integer",
     is_nullable => 0
   });

__PACKAGE__->belongs_to("child" => "Schema::Categories", "child");
__PACKAGE__->belongs_to("parent" => "Schema::Categories", "parent");
__PACKAGE__->add_unique_constraint([ qw/child parent/ ]);


1;