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 都指向员工表.

推荐答案

不需要在模型上设置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屋！