* 自己結合 [#c9b292e5]

*** 自己結合1 [#c1729252]
 SELECT XXX FROM category cate1 JOIN category cate2 ON cate1.parent_category_id = cate2.category_id
 SELECT XXX FROM category me JOIN category cate2 ON cate1.parent_category_id = cate2.category_id

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

*** 自己結合2 [#f2db902b]
 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 [#r9685ba3]
 $rs->search(undef,{prefetch => 'user', join => 'user'});
ジョインするテーブルをjoin=>'TABLE名'で明示する事。

トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 検索 最終更新   ヘルプ   最終更新のRSS