node.js - 如何在 sequelize 中加入 4 个或更多表？

Question

我正在使用 sequelize 使用现有数据库重写一个应用程序。我已将现有表转换为 Sequelize 模型，如下所示。我有员工表和访问类型模型，它在管理员访问中具有读、写或两者。所有这些访问都针对员工 in 和员工访问表进行跟踪。但是还有一个员工组表，它会跟踪员工是否属于特定组及其访问类型。我如何使用 sequelize 查询，从employeeAccess 表中获取特定员工访问列表，以及它是否属于组以及它是否确实访问该组的类型。所以本质上，需要通过加入 4 个表来查询 - 员工，员工访问，员工组，然后访问类型表。

员工模型

    var employee = db.seq.define('Employee',{
        id: { type: db.Sequelize.INTEGER},
        managerId: { type: db.Sequelize.INTEGER}, 
        employee_name: { type: db.Sequelize.STRING} ...
    });
    employee.hasOne(address);
    employee.belongsTo(employee, { foreignKey: 'managerId', as: 'Manager' }); 

地址模型

    var adress= db.seq.define("Adresss",{
        employee_id: { type: db.Sequelize.INTEGER}...
    });
    address.belongsto(employee); 

访问类型模型

    var accessType = db.seq.define('AccessType ',{
        id: { type: db.Sequelize.INTEGER},
        type: { type: db.Sequelize.STRING}....
    });
    accessType.hasMany(employeeGroup );
    accessType.hasMany(employeeAccess);

员工组模型

    var EmployeeGroup = db.seq.define('EmployeeGroup ',{
        id: { type: db.Sequelize.INTEGER},
        access_type_id: { type: db.Sequelize.INTEGER},
        employee_id: { type: db.Sequelize.INTEGER} ...
    });
    EmployeeGroup .belongsto(employee);
    EmployeeGroup .belongsto(accessType ); 

员工访问模型

    var employeeAccess = db.seq.define('employeeAccess ',{
        id: { type: db.Sequelize.INTEGER},
        access_type_id: { type: db.Sequelize.INTEGER},
        employee_id: { type: db.Sequelize.INTEGER} ...
    });
    employeeAccess.belongsto(employee); 
    employeeAccess.belongsto(accessType ); 

sql

select * from employeeAcess as a 
left outer join employeeGroup as g 
on a.access_type_id = g.access_type_id 
left outer join accesstype as t 
on g.access_type_id = t.id 
where a.employee_id = 1 and g.employee_id = 1; 

Answer 1

尝试这样的事情:

const employeeItem = await employee .findById(1, {
  include: [{ 
    model: employeeGroup,
    include: [accessType]
  }, employeeAccess],
  where: {
    '$employeeGroup.access_type_id$': sequelize.col('employeeAccess.access_type_id')
  }
}