Perl-DBIC/複雑なSQL/複雑なSQL2 のバックアップ(No.1)

バックアップ一覧
差分を表示
現在との差分を表示
ソースを表示
Perl-DBIC/複雑なSQL/複雑なSQL2 へ行く。
- 1 (2007-04-09 (月) 01:32:10)

DBIC 自己結合

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

前提

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

categoryテーブル

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

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

取得したいデータ

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

例：

カテゴリID	カテゴリ名	ペアレントID	ペアレントカテゴリ名
15	日本史	3	社会
16	世界史	3	社会
17	地理	3	社会
20	物理	4	理科
21	化学	4	理科
22	地学	4	理科
23	生物	4	理科

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()を使う。