从MySQL结果和PHP for D3.js树创建分层JSON？

从MySQL结果和PHP for D3.js树创建分层JSON？

这看起来不像是分层数据的合理设计。考虑另一种方法，例如 邻接表 。

解决方案＃1-MySQL 8 JSON支持：

在MySQL
8中，您可以使用

JSON_ARRAYAGG()

和

JSON_OBJECT()

仅通过SQL获得JSON结果：

select json_object(  'name', l1.level_1_name,  'children', json_arrayagg(json_object('name', l2.level_2_name, 'children', l2.children))) as jsonfrom level_1 l1left join (  select l2.level_2_name       , l2.level_1_fk       , json_arrayagg(json_object('name', l3.level_3_name)) as children  from level_2 l2  left join level_3 l3 on l3.level_2_fk = l2.level_2_pk  group by l2.level_2_pk) l2 on l2.level_1_fk = l1.level_1_pkgroup by level_1_pk

结果是：

{"name": "Bob", "children": [{"name": "Ted", "children": [{"name": "Fred"}]}, {"name": "Carol", "children": [{"name": "Harry"}]}, {"name": "Alice", "children": [{"name": "Mary"}]}]}

db-fiddle演示

格式：

{  "name": "Bob",  "children": [    {      "name": "Ted",      "children": [        {          "name": "Fred"        }      ]    },    {      "name": "Carol",      "children": [        {          "name": "Harry"        }      ]    },    {      "name": "Alice",      "children": [        {          "name": "Mary"        }      ]    }  ]}

解决方案2-使用GROUP_CONCAT（）构造JSON：

如果名称中不包含引号，则可以使用

GROUP_CONCAt()

以下版本手动构建JSON字符串：

$query = <<<MySQL    select concat('{',      '"name": ', '"', l1.level_1_name, '", ',      '"children": ', '[', group_concat(        '{',        '"name": ', '"', l2.level_2_name, '", ',        '"children": ', '[', l2.children, ']',        '}'      separator ', '), ']' '}') as json    from level_1 l1    left join (      select l2.level_2_name, l2.level_1_fk, group_concat('{', '"name": ', '"',  l3.level_3_name, '"', '}') as children      from level_2 l2      left join level_3 l3 on l3.level_2_fk = l2.level_2_pk      group by l2.level_2_pk    ) l2 on l2.level_1_fk = l1.level_1_pk    group by level_1_pkMySQL;

结果将是相同的（请参阅demo）

解决方案＃3-使用PHP对象构造嵌套结构：

您还可以编写一个更简单的SQL查询并在PHP中构造嵌套结构：

$result = $connection->query("    select level_1_name as name, null as parent    from level_1    union all    select l2.level_2_name as name, l1.level_1_name as parent    from level_2 l2    join level_1 l1 on l1.level_1_pk = l2.level_1_fk    union all    select l3.level_3_name as name, l2.level_2_name as parent    from level_3 l3    join level_2 l2 on l2.level_2_pk = l3.level_2_fk");

结果是

name    | parent----------------Bob     | nullTed     | BobCarol   | BobAlice   | BobFred    | TedHarry   | CarolMary    | Alice

演示

注意：该名称在所有表中都应该是唯一的。但是，如果有可能重复，我不知道您会期待什么结果。

现在，将行另存为对象，并以名称索引：

$data = []while ($row = $result->fetch_object()) {    $data[$row->name] = $row;}

$data

现在将包含

[    'Bob'   => (object)['name' => 'Bob',   'parent' => NULL],    'Ted'   => (object)['name' => 'Ted',   'parent' => 'Bob'],    'Carol' => (object)['name' => 'Carol', 'parent' => 'Bob'],    'Alice' => (object)['name' => 'Alice', 'parent' => 'Bob'],    'Fred'  => (object)['name' => 'Fred',  'parent' => 'Ted'],    'Harry' => (object)['name' => 'Harry', 'parent' => 'Carol'],    'Mary'  => (object)['name' => 'Mary',  'parent' => 'Alice'],]

现在，我们可以在单个循环中链接节点：

$roots = [];foreach ($data as $row) {    if ($row->parent === null) {        $roots[] = $row;    } else {        $data[$row->parent]->children[] = $row;    }    unset($row->parent);}echo json_enpre($roots[0], JSON_PRETTY_PRINT);

结果：

{    "name": "Bob",    "children": [        { "name": "Ted", "children": [     {         "name": "Fred"     } ]        },        { "name": "Carol", "children": [     {         "name": "Harry"     } ]        },        { "name": "Alice", "children": [     {         "name": "Mary"     } ]        }    ]}

演示

如果可能有多个根节点（中有多行

level_1_name

），请使用

json_enpre($roots);