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

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

前提

カテゴリは上位のカテゴリとサブカテゴリが存在する。例えば、カテゴリには国語、数学、社会、理科、英語があり、理科カテゴリのサブカテゴリとして物理、化学、地学、生物があるような。カテゴリは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()を使う。

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

Last-modified: 2011-06-16 (木) 11:40:40 (2653d)