从MySQL结果和PHP for D3.js树创建分层JSON? [英] Creating hierarchical JSON from MySQL results and PHP for D3.js tree?
问题描述
我正在尝试使用PHP从数据库结果中创建以下JSON(大大简化了...):
I am trying to create the following JSON (much simplified...) from database results using PHP:
{
"name": "Bob",
"children": [{
"name": "Ted",
"children": [{
"name": "Fred"
}]
},
{
"name": "Carol",
"children": [{
"name": "Harry"
}]
},
{
"name": "Alice",
"children": [{
"name": "Mary"
}]
}
]
}
数据库表:
Table 'level_1':
level_1_pk| level_1_name
-------------------------
1 | Bob
Table 'level_2':
level_2_pk| level_2_name | level_1_fk
-------------------------
1 | Ted | 1
2 | Carol | 1
3 | Alice | 1
Table 'level_3':
level_3_pk| level_3_name | level_2_fk
-------------------------
1 | Fred | 1
2 | Harry | 2
3 | Mary | 3
代码:
$query = "SELECT *
FROM level_1
LEFT JOIN level_2
ON level_1.level_1_pk = level_2.level_1_fk";
$result = $connection->query($query);
while ($row = mysqli_fetch_assoc($result)){
$data[$row['level_1_name']] [] = array(
"name" => $row['level_2_name']
);
}
echo json_encode($data);
产生:
{"Bob":[{"name":"Ted"},{"name":"Carol"},{"name":"Alice"}]}
问题:
如何获得下一个级别level_3，并按照上面定义的JSON的要求在JSON中包含文本"children"和level_3子级?
How can I get the next level, level_3, and include the text "children" and level_3 children in the JSON as required in the JSON defined above?
我想在JSON中有更多子级的情况下，我将需要PHP是递归的.
I imagine I will need the PHP to be recursive given more children in the JSON.
推荐答案
这看起来不太像分层数据设计.考虑另一种方法，例如邻接表.
This doesn't look like a decent design for hierarchical data. Consider another approach like adjacency list.
在MySQL 8中，您可以使用 JSON_ARRAYAGG() 和 JSON_OBJECT() 仅使用SQL获取JSON结果:
With MySQL 8 you can use JSON_ARRAYAGG() and JSON_OBJECT() to get the JSON result with SQL only:
select json_object(
'name', l1.level_1_name,
'children', json_arrayagg(json_object('name', l2.level_2_name, 'children', l2.children))
) as json
from level_1 l1
left 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_pk
group by level_1_pk
结果是:
{"name": "Bob", "children": [{"name": "Ted", "children": [{"name": "Fred"}]}, {"name": "Carol", "children": [{"name": "Harry"}]}, {"name": "Alice", "children": [{"name": "Mary"}]}]}
格式化:
{
"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字符串:
Solution #2 - Constructing JSON with GROUP_CONCAT():
If the names don't contain any quote carachters, you can manually construct the JSON string in older versions using GROUP_CONCAT():
$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_pk
MySQL;
结果将是相同的(请参见演示)
The result would be the same (see demo)
您还可以编写一个更简单的SQL查询并在PHP中构造嵌套结构:
You can also write a simpler SQL query and construct the nested structure in 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 | null
Ted | Bob
Carol | Bob
Alice | Bob
Fred | Ted
Harry | Carol
Mary | Alice
注意:名称在所有表中都应该是唯一的.但是，如果有可能重复，我不知道会得到什么结果.
Note: The name should be unique along all tables. But I don't know what result you would expect, if duplicates were possible.
现在将行另存为对象，并以名称索引:
Now save the rows as objects in an array indexed by the name:
$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'],
]
我们现在可以在单个循环中链接节点:
We can now link the nodes in a single loop:
$roots = [];
foreach ($data as $row) {
if ($row->parent === null) {
$roots[] = $row;
} else {
$data[$row->parent]->children[] = $row;
}
unset($row->parent);
}
echo json_encode($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中有多个行)，请使用
If multiple root nodes are possible (multiple rows in level_1_name), then use
json_encode($roots);
这篇关于从MySQL结果和PHP for D3.js树创建分层JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
