javascript - 使用 Knex,如何将连接表的相关行作为嵌套对象给出?
问题描述
使用 knex,如何从users
表中获取用户的相关行以及 id=1 的用户的所有组的数组?
…我正在运行此查询,但它为同一用户返回 3 个单独的行:
db("users").join("users_groups", "users.id", "=", "users_groups.user_id").join("groups", "groups.id", "=", "users_groups.group_id").where("users.id", "=", 1)
我相信这可以转化为:
select * from users inner join users_groups on users.id = users_groups.user_id inner join groups on groups.id = users_groups.group_id where users.id=1
它目前正在返回:
Array(3) [Object, Object, Object]
length:3
__proto__:Array(0) [, …]
0:Object {email:"raj@raj.raj" group_id:1, id:1, name:"step 1", name:"r", role:"superadmin", user_id:1, username:"raj"}
1:Object {email:"raj@raj.raj" group_id:2, id:1, name:"step 2", name:"r", role:"superadmin", user_id:1, username:"raj"}
2:Object {email:"raj@raj.raj" group_id:3, id:1, name:"step 3", name:"r", role:"superadmin", user_id:1, username:"raj"}
字符串化,看起来像这样
"[{"id":1,"name":"step 1","email":"raj@raj.raj","username":"raj","password":"$2b$10$GbbLTP2sEPS7OKmR4l8RSeX/PUmoIFyNBJb1RIIIrbZa1NNwolHFK","role":"superadmin","created_at":"2020-04-14T12:45:38.138Z","user_id":1,"group_id":1},{"id":2,"name":"step 2","email":"raj@raj.raj","username":"raj","password":"$2b$10$GbbLTP2sEPS7OKmR4l8RSeX/PUmoIFyNBJb1RIIIrbZa1NNwolHFK","role":"superadmin","created_at":"2020-04-14T12:45:38.138Z","user_id":1,"group_id":2},{"id":3,"name":"step 3","email":"raj@raj.raj","username":"raj","password":"$2b$10$GbbLTP2sEPS7OKmR4l8RSeX/PUmoIFyNBJb1RIIIrbZa1NNwolHFK","role":"superadmin","created_at":"2020-04-14T12:45:38.138Z","user_id":1,"group_id":3}]"
我宁愿让它返回一个代表单个用户行的对象,并为groups
表中的 3 个相关行提供一个嵌套对象。例如:
{id:1, name:"raj", groups:[{id:1, name:"step 1"}, {id:2,name:"step 2"}, {id:3,name:"step 3"}]}
这可能吗?或者是否需要多个查询,这有多浪费?
解决方案
Knex 无法根据需要聚合平面数据。你应该自己做。
(await db('users')
.join('users_groups', 'users.id', '=', 'users_groups.user_id')
.join('groups', 'groups.id', '=', 'users_groups.group_id')
.where('users.id', '=', 1)
)
.reduce((result, row) => {
result[row.id] = result[row.id] || {
id: row.id,
username: row.username,
email: row.email,
groups: [],
};
result[row.id].groups.push({ id: row.group_id, name: row.name });
return result;
}, {});
推荐阅读
- java - Jackson,如何使用原始类型将 json 转换为 Java 类?
- azure - Azure DevOps 部署到本地计算机
- python - 熊猫中 for 循环的矢量化
- c++ - 在c ++中逐行拆分段落
- javascript - how to run a function when context changes in react
- c++ - How do you use the MySQL Connector/C++ and XDevAPI while using XAMPP to run MySQL?
- swift - Passing Index of a selected row to another ViewController
- c# - Azure 媒体服务作业长时间处于处理状态
- c - 为子类添加额外的“方法”
- android - Blank screen after Expo publish