カテゴリテーブルの並び替え

質問

カテゴリテーブルを以下のように持っているとします

id（自動連番）
parent（親は0、子は属する親のid）
name（カテゴリ名）
order（並び順）

並び順は親が0から連番が振られていて、子は属する親毎に0から連番が振られています。
例えば以下のようなデータです。

id | parent | name | order
1 | 0 | 食べ物 | 1
2 | 0 | 飲み物 | 0
3 | 0 | その他 | 2
4 | 1 | 米 | 0
5 | 2 | 水 | 0
6 | 3 | 炭 | 0
7 | 1 | パン | 1

このときに、親カテゴリにに続いて子カテゴリが表示され、その次にorder順の親、子という表示方法にするには、どのように抽出すればよいでしょうか。

○理想の並び順
飲み物
水
食べ物
米
パン
その他
炭

できれば、後でいろいろと並び替えたりもしたいので、orderの後にSELECT時にフィールドを追加して、サブクエリとして使えるようなSQL文だと嬉しいのですが。

言わんとしている事が伝われば良いのですが、おわかりになる方よろしくお願いします。

回答No.1

mpro-gram

parent_id だけ指定する隣接型ツリー　といわれる形式ですね。
自己結合により、経路列挙型のデータを作って、経路で並べ変えれば、求める並び順だと思います。
階層の数だけ自己結合が必要なので、ご呈示のデータだと３階層になるので３個自己結合必要です。
さらに、同一階層内での並び順が、自前のid順ではなく 親id内でのorderになるので、親idとorder用カラムとを連結して、並べ替えデータとする必要があります。

また、数値を文字列連結する場合は、桁数をそろえないと順番が狂ってしまうので、この工夫も必要です。
あと、order は予約語なので、カラム名に使うときは必ず `order` とくくらなければならなくなります。

とりあえず以下では 階層内順番用カラム名はord、テーブル名は adj とした。
parent は２桁がありうる前提で処理し、ord は１桁の前提で桁あわせの処理は省いた。_ は経路表示したときに、桁のずれを判定しやすくするため入れてある。

select self.name
, concat('_', ifnull( concat( RIGHT(concat('00',p2.parent),2), p2.ord,'_' ) , '')
, ifnull( concat( RIGHT(concat('00',p1.parent),2), p1.ord,'_' ) , '')
, RIGHT(concat('00',self.parent),2), self.ord ,'_'
) as path
from adj as self left join adj as p1 on self.parent = p1.id
left join adj as p2 on p1.parent = p2.id
order by path ;