Perl-DBIC/テーブル結合/自己結合のバックアップ(No.1)

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
Perl-DBIC/テーブル結合/自己結合へ行く。
- 1 (2010-06-07 (月) 05:56:42)
- 2 (2011-06-16 (木) 11:34:36)

テーブル結合

JOIN （3つのテーブル）

 SELECT order.* FROM order JOIN order_detail ON order.id = order_detail.order_id JOIN goods order_detail.goods_id = goods.id
 WHERE goods.name = 'book'

 $rs->search({
  "goods.name" => 'book',
 },{ 
   join => {order_detail => 'goods'}
 });

自己結合

 SELECT XXX FROM category cate1 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' } ]
         ]
     }
 );

自己結合

 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名'で明示する事。