javascript - 带有异议 JS 的 INNER JOIN 查询?
问题描述
我得到了以下数据模型:https ://imgur.com/a/AwwpW9F
基本上, AUser
可以属于 many Projects
,并且 aProject
可以拥有 many Users
,我通过一个名为UserProjects
使用原始 SQL 查询,我可以去
SELECT "user".email, project.name FROM "user"
JOIN userprojects ON userprojects.user_id = "user".id
JOIN project ON project.id = userprojects.project_id
这给了我
email(On User table) name(On Project table)
first@email.com Project X
first@email.com Project Y
second@email Project Y
我将如何使用 Objection ORM 构建此查询?也许我可以直接做一个原始查询?就像是
User.query().raw(SELECT "user".email, project.name FROM "user"
JOIN userprojects ON userprojects.user_id = "user".id
JOIN project ON project.id = userprojects.project_id)
?
解决方案
Objection.js 可以为您完成所有工作,而不是自己做所有的事情。你可以只声明一个ManyToManyRelation
.
static relationMappings = {
projects: {
relation: Model.ManyToManyRelation,
modelClass: Project, // imported Objection class of "Project"
join: {
from: 'user.id',
through: {
from: 'userprojects.user_id',
to: 'userprojects.project_id'
},
to: 'project.id'
}
}
}
然后,您可以使用预加载获取用户的项目:
User.query().eager('projects').findById(userId)
你会得到类似的东西:
User {
id: 3,
firstname: 'firstname',
lastname: 'lastname',
email: 'email',
projects: [
{id: 1,
name: 'name1'},
{id: 2,
name: 'name2'},
]
}
2020 年更新:
从版本 2 开始Objection.js
,eager
方法已重命名为withGraphFetched
:
User.query().withGraphFetched('projects').findById(userId)