首頁>Program>source

我正在使用sequelize用現有資料庫重寫一个app.我已经將現有表轉換為以下續集模型.我有employee表和訪問型別模型,在管理員訪問權限中具有讀,寫或两者兼有.所有這些訪問都將在"員工訪問"表和"員工訪問"表中进行跟蹤.但是也有一个雇員組表,如果雇員屬於一个特定的組及其訪問型別,它会跟蹤.我该如何使用sequelize进行查詢,以从employeeAccess表中获取特定的員工訪問列表,以及该列表是否屬於该組以及是否訪問该組的型別.因此从本質上讲,需要通過聯接4个表进行查詢-員工,員工訪問,員工組以及訪問型別表。

  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. accessType模型
   var accessType = db.seq.define('AccessType ',{
        id: { type: db.Sequelize.INTEGER},
        type: { type: db.Sequelize.STRING}....
    });
    accessType.hasMany(employeeGroup );
    accessType.hasMany(employeeAccess);
  1. EmployeeGroup模型
   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;
最新回復
  • 12天前
    1 #

    尝試這樣的事情:

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

  • symfony:使用事件监听器重定向所有"找不到路由404找不到-NotFoundHttpException"
  • python:如何提取具有混合值的行