配列の階層構造をデータベースから復元する

問題

以下の階層構造を

[
    'name' => 'A',
    'children' => [
        [
            'name' => 'B',
            'children' => [
                [
                    'name' => 'C',
                    'children' => [],
                ],
            ],
        ],
        [
            'name' => 'D',
            'children' => [],
        ]
    ],
]

以下のようなテーブルcategoriesで表現している．

id	name	parent_id
1	A	NULL
2	B	1
3	D	1
4	C	2

このテーブルに対してSQLを発行して，もとの配列の階層構造をできるだけ効率良く復元したい．さてどうしよう？

解決策

再帰クエリを使いましょう．以下の例では，$_GET['id']を受け取ってそこからの階層をJSONで表示します．

<?php

// Content-TypeをUTF-8エンコードされたJSONであるとして明示
header('Content-Type: application/json; charset=UTF-8');

try {

    // データベースに接続
    $pdo = new \PDO('sqlite:example.db', '', '', [
        \PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION,
        \PDO::ATTR_DEFAULT_FETCH_MODE => \PDO::FETCH_ASSOC,
    ]);

    // 再帰的に子ノードを取得するSQLを用意
    $stmt = $pdo->prepare("
        WITH RECURSIVE r AS (
            SELECT * FROM categories WHERE id = ?
            UNION ALL
            SELECT categories.* FROM r, categories WHERE r.id = categories.parent_id
        )
        SELECT id, name, parent_id FROM r
    ");

    // $_GET['id'] を確実に整数として「?」にバインドする (未定義の場合はゼロ) 
    $stmt->bindValue(1, (int)filter_input(INPUT_GET, 'id'), PDO::PARAM_INT);

    // SQLを実行
    $stmt->execute();

    // ルートノードを取得
    $row = $stmt->fetch();
    if ($row === false) {
        throw new \RuntimeException('Not Found', 404);
    }
    $root = [
        'name' => $row['name'],
        'children' => [],
    ];
    $map = [$row['id'] => &$root];
    unset($row);

    // その他のノードを配置する
    foreach ($stmt as $row) {
        $node = [
            'name' => $row['name'],
            'children' => [],
        ];
        $map[$row['id']] = &$node;
        $map[$row['parent_id']]['children'][] = &$node;
        unset($row, $node);
    }
    unset($map);

    // 結果を表示
    echo json_encode($root, JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

} catch (\PDOException $e) {

    // PDOがスローした例外
    http_response_code(500);
    echo json_encode([
        'error' => $e->getMessage(),
    ], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

} catch (\RuntimeException $e) {

    // 自分でスローした例外
    http_response_code($e->getCode() ?: 500);
    echo json_encode([
        'error' => $e->getMessage(),
    ], JSON_UNESCAPED_UNICODE | JSON_PRETTY_PRINT);

}

SQLiteデータベース作成用シェルコマンド

echo "
    CREATE TABLE IF NOT EXISTS categories(
        id INTEGER PRIMARY KEY NOT NULL,
        name TEXT NOT NULL,
        parent_id INTEGER
    );
    INSERT INTO categories
        SELECT 1, 'A', NULL
        UNION ALL
        SELECT 2, 'B', 1
        UNION ALL
        SELECT 3, 'D', 1 
        UNION ALL
        SELECT 4, 'C', 2
    ; 
" | sqlite3 example.db

「連想配列の配列」か？「オブジェクトの配列」か？

以下の部分のコードですが…

$root = [
    'name' => $row['name'],
    'children' => [],
];
$map = [$row['id'] => &$root];
unset($row);

foreach ($stmt as $row) {
    $node = [
        'name' => $row['name'],
        'children' => [],
    ];
    $map[$row['id']] = &$node;
    $map[$row['parent_id']]['children'][] = &$node;
    unset($row, $node);
}
unset($map);

もし配列にこだわらずにstdClassを含んでいても問題が無い場合は，おそらくこちらのほうが読みやすくなるでしょう．オブジェクトの場合は配列と違って参照型なので，&を使って**参照代入(参照渡し)**する必要がありません．従って明示的なunsetも要りません．json_encodeする場合はこちらで全く問題無いです．

$root = (object)[
    'name' => $row['name'],
    'children' => [],
];
$map = [$row['id'] => $root];

foreach ($stmt as $row) {
    $map[$row['parent_id']]->children[] = $map[$row['id']] = (object)[
        'name' => $row['name'],
        'children' => [],
    ];
}

そもそも\PDO::FETCH_ASSOCを\PDO::FETCH_OBJにする場合はこうなりますね．もっとスッキリします．

$root = (object)[
    'name' => $row->name,
    'children' => [],
];
$map = [$row->id => $root];

foreach ($stmt as $row) {
    $map[$row->parent_id]->children[] = $map[$row->id] = (object)[
        'name' => $row->name,
        'children' => [],
    ];
}

余談ですが，PHP7からarray_columnが微妙に強化されています．

これにより，今後この関数を使いたい場合においても，「連想配列の配列」に拘る必要は無くなっていくと思われます．「オブジェクトの配列」のほうがよりシンプルに書けますしね．

連想配列の配列

$first_child_of_first_child = $node['children'][0]['children'][0];
$children_of_children = array_column($node['children'], 'children'); // PHP5.5+

オブジェクトの配列

$first_child_of_first_child = $node->children[0]->children[0];
$children_of_children = array_column($node->children, 'children'); // PHP7.0+

PHP5を切り捨ててもいい場合は，json_decodeする場合も第2引数に思考停止trueを渡すのは避けるようにしていきましょう．