複雑なSQL2 再帰的なデータ構造

再帰的なデータ構造のテーブルに対し、自己結合を使ってデータを取得する。

前提

カテゴリは上位のカテゴリとサブカテゴリが存在する。例えば、カテゴリには国語、数学、社会、理科、英語があり、理科カテゴリのサブカテゴリとして物理、化学、地学、生物があるような。カテゴリは2段階までで、サブサブカテゴリは存在しないとする。

categoryテーブル

以下のようなカラムを持つ。category_idとparent_category_idが等しい場合、それは上位のカテゴリである。

• category_id (int)(PK)
• category_name (text)
• parent_category_id (int)

取得したいデータ

以上の条件で、すべてのカテゴリIDとそのペアレントID、およびそれぞれのカテゴリ名を取得したい。

例：

SQL

SQLは以下の通り。

 select 
 cate1.category_id as cate_id, cate1.category_name as cate_name, 
 cate1.parent_category_id as p_cate_id, cate2.category_name as p_cate_name 
 from 
   category cate1
   join category cate2 
    on cate1.parent_category_id = cate2.category_id 
  where
    cate1.category_id <> cate1.parent_category_id
  order by cate1.parent_category_id, cate1.category_id;

DBIC

DBICのコードは以下の通り。

 $c_rs = $c_rs->search_literal(
     'cate1.category_id <> cate1.parent_category_id'
     , { 
         from => [
             {cate1 => 'category'},
             [   
                 {cate2 => 'category'},
                 {'cate1.parent_category_id' => 'cate2.category_id'}
             ],
         ],
         select => [
             'cate1.category_id',
             'cate1.category_name',
             'cate1.parent_category_id',
             'cate2.category_name',
         ],
         as => [qw/
             category_id
             category_name
             parent_category_id
             parent_category_name
         /],
         order_by => [qw/
             cate1.parent_category_id
             cate1.category_id
         /]
     }
 );

ポイント

• fromを使って結合するテーブルを指定する。その際、joinの条件も書く。
• category_id <> parent_category_idのような条件はsearch()では出来ないっぽいので、search_literal()を使う。