sequelize中一张表中同一张表的两个外键 [英] Two foreign Key of same table in one table in sequelize

问题描述

我的团队成员模型:-

var teamMember = {
    id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true
    },
    level: DataTypes.INTEGER,
    supervisorId: {
        type: DataTypes.INTEGER,
        references: {
            model: "employees",
            key: "id"
        }
    },
    employeeId: {
        type: DataTypes.INTEGER,
        unique: true,
        references: {
            model: "employees",
            key: "id"
        }
    }

还有员工模式

映射:-

db.employee.hasOne(db.teamMember);
db.teamMember.belongsTo(db.employee);

我的查询函数

        db.teamMember.findOne({
        where: { employeeId: req.employee.id },
        include: [db.employee]

    })
    .then(teamMember => {
        if (!teamMember) {
            throw ('no teamMember found');
        }
       consol.log(teamMember)
    })

我的 teamMember 表就像=

id------employeeId-----supervisorId

id------employeeId------supervisorId

2 ------------ 4 ------------- 5

2 ----------- 4 ------------- 5

问题是 -:所以当我在 teamMember 中询问其employeeId 为 4 的行时，它应该包含在 supervisorId(JOIN) 中，并且它返回包含 4 (id) 的员工的行.我想要第 5 个 ID 的员工.

Problem is -: so when i m asking for row in teamMember whose employeeId is 4. that should be include with supervisorId(JOIN) and it returns row with employee included of 4 (id) . i want employee of 5th id .

supervisorId 和employeeId 都参考员工表.

推荐答案

model 上不需要设置employee 和 supervisor 的字段，只要做belongTo 就可以添加了，这里可以指定if是唯一的并使用as"，因此您可以与员工一起知道您在加入时指的是普通员工还是主管，如下所示:

You don't need to set the fields of employee and supervisor on the model, just doing the belongTo it will add it, and there you can specify the if is unique and use "as" so you can know with employee are you refering on the join, the regular employee or the supervisor, something like this:

db.teamMember.belongsTo(db.employee, {as: 'SupervisorId'});
db.teamMember.belongsTo(db.employee, {as: 'RegularEmployeeId'});

然后在您的查询中添加这样的包含:

and then on your query add the include like this:

include: [{
    model: db.employee,
    as: 'SupervisorId
}]

这篇关于sequelize中一张表中同一张表的两个外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！