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

# 問題

以下の階層構造を

```php:
[
    '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
<?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);

}
```


```bash: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
```

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

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

```php
$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`する場合はこちらで全く問題無いです．

```php
$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`にする場合はこうなりますね．もっとスッキリします．

```php
$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](http://www.php.net/manual/ja/function.array-column.php)が微妙に強化されています．

![スクリーンショット 2016-03-24 16.27.48.png](https://qiita-image-store.s3.amazonaws.com/0/25060/17ebe922-5b75-0b40-ddf9-d0f9d01bf53f.png)
![スクリーンショット 2016-03-24 16.27.37.png](https://qiita-image-store.s3.amazonaws.com/0/25060/523023e5-5abb-7cc1-e189-9ea89812d2b3.png)

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

```php:連想配列の配列
$first_child_of_first_child = $node['children'][0]['children'][0];
$children_of_children = array_column($node['children'], 'children'); // PHP5.5+
```

```php:オブジェクトの配列
$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`を渡すのは避けるようにしていきましょう．