php - 如何 SQL 查询特定 JSON 格式的父子节点?
问题描述
我期待这个 JSON 用于我的 jQuery 代码:
{
"projects": [
{
"id": "1",
"project_name": "Carmichael House",
"parent_id": "0",
"children": [
{
"id": "2",
"project_name": "Carmichael Kitchen",
"parent_id": "1"
},
{
"id": "3",
"project_name": "Carmichael Bathroom",
"parent_id": "1"
}
]
},
{
"id": "2",
"project_name": "Dowd Apartment",
"parent_id": "0",
"children": [
{
"id": "4",
"project_name": "Dowd Kitchen",
"parent_id": "2"
}
]
}
]
}
此数据将来自 MySql 表tbl_projects
:
id
project_name
parent_id
SQLSELECT
查询应该是什么,以便输出1 个可轻松转换为 JSON(在 PHP 或 JavaScript/jQuery 中)的平面表?
我是否以正确的方式接近这个?
解决方案
您可以直接从 MySQL 生成 JSON 内容。这是一个适用于 MySQL 5.7 或更高版本的解决方案。
作为初学者,请考虑functionJSON_OBJECT()
,它为表中的每条记录生成一个 JSON 对象:
SELECT
p.*,
JSON_OBJECT('id', id, 'project_name', project_name, 'parent_id', parent_id) js
FROM tbl_projects p;
给定您的样本数据,这将返回:
| id | project_name | parent_id | js |
| --- | ------------------- | --------- | ---------------------------------------------------------------- |
| 1 | Carmichael House | 0 | {"id": 1, "parent_id": 0, "project_name": "Carmichael House"} |
| 2 | Carmichael Kitchen | 1 | {"id": 2, "parent_id": 1, "project_name": "Carmichael Kitchen"} |
| 3 | Carmichael Bathroom | 1 | {"id": 3, "parent_id": 1, "project_name": "Carmichael Bathroom"} |
| 4 | Dowd Apartment | 0 | {"id": 4, "parent_id": 0, "project_name": "Dowd Apartment"} |
| 5 | Dowd Kitchen | 4 | {"id": 5, "parent_id": 4, "project_name": "Dowd Kitchen"} |
为了生成您预期的输出,我们将自JOIN
表查找子记录,并使用聚合函数JSON_ARRAYAGG()
生成内部 JSON 数组。额外的聚合级别将所有内容都塞入一个对象中。如您的示例数据所示,我假设根项目具有parent_id = 0
并且只有一个层次结构:
SELECT JSON_OBJECT('projects', JSON_ARRAYAGG(js)) results
FROM (
SELECT JSON_OBJECT(
'id', p.id,
'project_name', p.project_name,
'parent_id', p.parent_id,
'children', JSON_ARRAYAGG(
JSON_OBJECT(
'id', p1.id,
'project_name', p1.project_name,
'parent_id', p1.parent_id
)
)
) js
FROM tbl_projects p
LEFT JOIN tbl_projects p1 ON p.id = p1.parent_id
WHERE p.parent_id = 0
GROUP BY p.id, p.project_name, p.parent_id
) x
产量:
| results |
| -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| {"projects": [{"id": 1, "children": [{"id": 2, "parent_id": 1, "project_name": "Carmichael Kitchen"}, {"id": 3, "parent_id": 1, "project_name": "Carmichael Bathroom"}], "parent_id": 0, "project_name": "Carmichael House"}, {"id": 4, "children": [{"id": 5, "parent_id": 4, "project_name": "Dowd Kitchen"}], "parent_id": 0, "project_name": "Dowd Apartment"}]} |
推荐阅读
- sql - Oracle 数据库错误 907:ORA-00907:缺少右括号
- php - CakePhp 2.x getClientIp 返回 ::1
- mysql - 如何将特定目录路径的持久卷挂载到 Openshift 中的默认 MySQL 数据目录
- php - Laravel Homestead 报告错误版本的 Vagrant,因此不允许我运行 VM
- java - 使用 Firebase Realtime 删除帐户
- amazon-emr - 在不关闭集群的情况下增加 EMR 主节点容量的解决方案
- android - Play 管理中心:“内部测试轨道”在完成审核后如何运作?
- elm - 如何在 ELM 应用程序中调用 API,我的代码不会处理响应数据?
- vue.js - 如何在 ant-design-vue 表中选择整行?
- java - 得墨忒耳定律不适用于这种情况?