自己結合

自己結合1

 SELECT XXX FROM category me JOIN category cate2 ON cate1.parent_category_id = cate2.category_id

 $rs = $rs->search({},
     {
         from => [
             { me => 'category' },
             [ { cate2 => 'category' }, { 'me.parent_category_id' => 'cate2.category_id' } ]
         ]
     }
 );

自己結合2

 SELECT 
   goods.*, 
   cate1.category_name AS category_name, cate2.category_name AS parent_category_name 
 FROM 
   goods 
 JOIN 
   category cate1 ON goods.category_id = cate1.category_id 
 JOIN 
   category AS cate2 ON cate1.parent_category_id = cate2.category_id
 WHERE 
   goods_id = 123

 my $rs = $self->schema->resultset('Goods')->search(
     { goods_id => 123 },
     {
         '+select' => [ 'cate1.category_name', 'cate2.category_name' ],
         '+as'     => [ 'category_name',       'parent_category_name' ],
         from      => [
             { me => 'goods' },
             [ { cate1 => 'category' }, { 'me.category_id'    => 'cate1.category_id' } ],
             [ { cate2 => 'category' }, { 'cate2.category_id' => 'cate1.parent_category_id' } ],
         ]
     }
 );
 
 $row = $rs->first;
 print $row->get_column('category_name');

$row->get_column('category_name')でなく$row->category_nameとしたい場合は、テーブル定義クラスに sub category_name{ shift->get_column('category_name) } というアクセサを追加しておけばよい。

PREFETCH

 $rs->search(undef,{prefetch => 'user', join => 'user'});

ジョインするテーブルをjoin=>'TABLE名'で明示する事。