带有自定义ON条件的左外部联接sequelize

Question

我使用sequelize，DB作为Postgres

我的学生表是(id，name，dept_id(列。

我的部门表为(id，dname，hod(列

我的协会看起来像这个

Student.hasOne(models.Department, {
foreignKey: 'id',
as : "role"
});

我想找一个有特定身份证和系里详细信息的学生。通过使用sequelize，我写了一个查询，如下

Student.findOne({
where: { id: id },
include: [{
model: Department,
as:"dept"
}],
})

仅供参考，findOne中有这个includes选项，它生成了一个左外部联接查询，如下所示

SELECT "Student"."id", "Student"."name", "Student"."dept_id", "dept"."id" AS "dept.id", "dept"."dname" AS "dept.dname", "dept"."hod" AS "dept.hod", FROM "students" AS "Student" 
LEFT OUTER JOIN "departments" AS "dept" 
ON "Student"."id" = "dept"."id" 
WHERE "Student"."id" = 1 
LIMIT 1;

我的预期输出应该是

{
id: 1,
name: "bod",
dept_id: 4,
dept: {
id: 4,
dname: "xyz",
hod: "x"
}
}

但我得到

{
id: 1,
name: "bod",
dept_id: 4,
dept: {
id: 1,
dname: "abc",
hod: "a"
}
}

那么如何将ON条件更改为

ON "Student"."dept_id" = "dept"."id"

提前感谢

Answer 1

Student.hasOne(models.Department, {
foreignKey: 'dept_id', // fixed
as : "role"
});