mysql - 带有连接的 SQL 查询以获取嵌套的对象数组
问题描述
摘要:我将从一个 JSON 模式开始描述期望。请注意具有嵌套对象数组的角色,我正在寻找可以获取单个查询的“智能查询”。
{
"id": 1,
"first": "John",
"roles": [ // Expectation -> array of objects
{
"id": 1,
"name": "admin"
},
{
"id": 2,
"name": "accounts"
}
]
}
用户
+----+-------+
| id | first |
+----+-------+
| 1 | John |
| 2 | Jane |
+----+-------+
角色
+----+----------+
| id | name |
+----+----------+
| 1 | admin |
| 2 | accounts |
| 3 | sales |
+----+----------+
用户角色
+---------+---------+
| user_id | role_id |
+---------+---------+
| 1 | 1 |
| 1 | 2 |
| 2 | 2 |
| 2 | 3 |
+---------+---------+
尝试01
在一种天真的方法中,我会在我的 nodejs 代码中运行两个 sql 查询,并借助multipleStatements:true
连接字符串。信息。
User.getUser = function(id) {
const sql = "SELECT id, first FROM user WHERE id = ?; \
SELECT role_id AS id, role.name from user_role \
INNER JOIN role ON user_role.role_id = role.id WHERE user_id = ?";
db.query(sql, [id, id], function(error, result){
const data = result[0][0]; // first query result
data.roles = result[1]; // second query result, join in code.
console.log(data);
});
};
问题:上面的代码产生了预期的 JSON 模式,但它需要两个查询,由于有多个语句,我能够在尽可能小的代码单元中缩小它,但我在 Java 或 C# 等其他语言中没有这样的奢侈例如,我必须创建两个函数和两个 sql 查询。所以我正在寻找一个单一的查询解决方案。
尝试02
在较早的尝试中,在 SO 社区的帮助下,我能够使用单个查询接近以下内容,但它只能帮助生成字符串数组(而不是对象数组)。
User.getUser = function(id) {
const sql = "SELECT user.id, user.first, GROUP_CONCAT(role.name) AS roles FROM user \
INNER JOIN user_role ON user.id = user_role.user_id \
INNER JOIN role ON user_role.role_id = role.id \
WHERE user.id = ? \
GROUP BY user.id";
db.query(sql, id, function (error, result) {
const data = {
id: result[0].id, first: result[0].first,
roles: result[0].roles.split(",") // manual split to create array
};
console.log(data);
});
};
尝试 02 结果
{
"id": 1,
"first": "John",
"roles": [ // array of string
"admin",
"accounts"
]
}
生成对象数组是一种常见的要求,所以想知道 SQL 中一定有我不知道的东西。有没有办法在最佳查询的帮助下更好地实现这一点。
或者让我知道没有这样的解决方案,就是这样,这就是它在生产代码中通过两个查询完成的方式。
尝试03
使用role.id
而不是role.name
in GROUP_CONCAT(role.id)
,这样您就可以获取一些 id,然后使用另一个子查询来获取关联的角色名称,只是想...
SQL(不起作用,只是为了思考一下)
SELECT
user.id, user.first,
GROUP_CONCAT(role.id) AS role_ids,
(SELECT id, name FROM role WHERE id IN role_ids) AS roles
FROM user
INNER JOIN user_role ON user.id = user_role.user_id
INNER JOIN role ON user_role.role_id = role.id
WHERE user.id = 1
GROUP BY user.id;
编辑
根据 Amit 的回答,我了解到SQL Server中有这样的解决方案JSON AUTO
,使用. 是的,这是我在 MySQL 中寻找的东西。
准确地表达。
连接表时,第一个表中的列将作为根对象的属性生成。第二个表中的列作为嵌套对象的属性生成。
解决方案
使用此加入查询
FOR JSON AUTO 将为您的查询结果返回 JSON
SELECT U.UserID, U.Name, Roles.RoleID, Roles.RoleName
FROM [dbo].[User] as U
INNER JOIN [dbo].UserRole as UR ON UR.UserID=U.UserID
INNER JOIN [dbo].RoleMaster as Roles ON Roles.RoleID=UR.RoleMasterID
FOR JSON AUTO
上述查询的输出是
[
{
"UserID": 1,
"Name": "XYZ",
"Roles": [
{
"RoleID": 1,
"RoleName": "Admin"
}
]
},
{
"UserID": 2,
"Name": "PQR",
"Roles": [
{
"RoleID": 1,
"RoleName": "Admin"
},
{
"RoleID": 2,
"RoleName": "User"
}
]
},
{
"UserID": 3,
"Name": "ABC",
"Roles": [
{
"RoleID": 1,
"RoleName": "Admin"
}
]
}
]
推荐阅读
- python - 使用 iloc 重新分配数据帧行或变量返回 NaN
- kotlin - 使用 @Configuration 注释的类可以隐式子类化,并且不能是最终的
- java - 如何在foreach java 8中执行算术运算
- android - 我已经使用了数据库引用,那么为什么我要将所有用户数据都放入 recyclerview 我只希望一个用户数据进入 recycler 视图?
- database - 如何在oracle中使用内壳?
- javascript - 是否可以自动密封 JS 对象?
- xcode - 在 SwiftUI 中使用 TabView 时应用程序崩溃
- git - Git 推送到上游失败,远程分支名称不同
- azure - Azure 客户端 ID 是全球唯一的吗?
- javascript - Laravel url 调用使用 javascript