如何在 sequelize 中加入 4 个或更多表? [英] how to join 4 or more tables in sequelize?

问题描述

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

I am rewriting an app , with existing database using sequelize. I have converted existing table, to models in sequelize as below. I have employee table, and access type model, which has read, write, or both in an admin access. all of these access are tracked for employee in and employee access table. but there is a employee group table also, which keeps track , if employee belongs to a particular group and their access type. how do i query using sequelize , to get particular employee access list from employeeAccess table, and also if it belongs to group and if it does access type of that group. so essentially, need to query by joining 4 tables - employee, employee access, employee group and then access type table.

1. 员工模型

    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' }); 

1. 地址模型

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

1. 访问类型模型

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

1. 员工组模型

    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 ); 

1. employeeAccess 模式

    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; 

推荐答案

试试这个:

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

这篇关于如何在 sequelize 中加入 4 个或更多表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！