sql - Sequelize : Including join table attributes in findAll include

Question

我目前正在开发一款用于管理公司分支机构和员工的应用程序，使用 NodeJS、MSSQL 和 Sequilize 作为 ORM。

要求以一种我们可以“返回”过去的日期并查看公司在该特定时间点的状态的方式来跟踪某些变化。为此，我们使用时间戳(initDate 和 endDate)来跟踪我们关心的更改，即分支机构的更改以及员工在不同部门之间的移动。活跃的分支机构。每次更改记录时，我们都会将其 endDate 设置为 now() 并使用更改和 endDate = null 创建一条新记录，因此每个分支或分支-员工关系的“当前”版本是 endDate IS NULL 的版本。

我们现在使用 Sequelize 面临的问题是:当我们的员工使用 findAll() 时，我们如何为分支员工关系指定 endDate -加载(即包含)他们的分支？在下面的示例中，我正在查询公司的“当前”状态，因此我要求 endDate: null，但这实际上是一个表示我们想要的时间点的参数。

非常感谢您的帮助。任何见解将不胜感激!

--- 这里我包括模型定义和查询 ---

分支模型定义:

var BranchModel = db.define<BranchInstance, Branch>('Branch', {
    IdBranch: {
        primaryKey: true,
        type: DataTypes.INTEGER
    },
    Name: DataTypes.STRING,
    // ... other fields ...
    initDate: DataTypes.DATE,
    endDate: DataTypes.DATE
}, {
    timestamps: false,
    classMethods: {
        associate: function (models) {

            Branch.belongsToMany(models.model('Employee'), {
                through: {
                    model: models.model('Branch_Employee')
                },
                as: 'Employees',
                foreignKey: 'branchId'
            });

            // ... other associations ..

        }
    }
});

员工模型定义:

var EmployeeModel = db.define<EmployeeInstance, Employee>('Employee', {
    idEmployee: {
        primaryKey: true,
        type: DataTypes.INTEGER
    },
    Name: DataTypes.STRING,
    // ... other fields ...
    active: DataTypes.BOOLEAN
}, {
    timestamps: false,
    defaultScope: {
        where: {
            active: true
        }
    },
    classMethods: {
        associate: function (models) {

            EmployeeModel.belongsToMany(models.model('Branch'), {
                through: {
                    model: models.model('Branch_Employee')
                },
                foreignKey: 'employeeId'
            });

            // ... other associations ...
        }
    }
});

连接表定义:

var Branch_Employee = db.define<BranchEmployeeInstance, BranchEmployee>('Branch_Employee', {
    branchId: DataTypes.INTEGER,
    employeeId: DataTypes.INTEGER
    // ... some other attributes of the relation ...
    initDate: DataTypes.DATE,
    endDate: DataTypes.DATE,
}, {
    timestamps: false,
    classMethods: {
        associate: function(models) {

            Branch_Employee.belongsTo(models.model('Employee'), {
                as: 'Employee',
                foreignKey: 'employeeId'
            });

            Branch_Employee.belongsTo(models.model('Branch'), {
                as: 'Branch',
                foreignKey: 'branchId'
            });
        }
    },
    instanceMethods: {
        // ... some instance methods ...
    }
});

序列化查询以获取每个活跃员工，急切地加载与他们相关的分支:

let employees = await EmployeeModel.findAll({
    logging: true,
    include: [{
        model: Branch,
        as: 'Branches',
        where: {
            endDate: null, // this would be [Branches].[endDate] IS NULL

            // i also need the generated query to include:
            // [Branches.Branch_Employee].[endDate] IS NULL

        },
        required: false
    }]
});

为上述查询生成的 SQL 如下所示:

SELECT [Employee].[idEmployee]
,[Employee].[Name]
,[Employee].[active]
,[Branches].[IdBranch] AS [Branches.IdBranch]
,[Branches].[Name] AS [Branches.Name]
,[Branches].[initDate] AS [Branches.initDate]
,[Branches].[endDate] AS [Branches.endDate]
,[Branches.Branch_Employee].[initDate] AS Branches.Branch_Employee.initDate]
,[Branches.Branch_Employee].[endDate] AS [Branches.Branch_Employee.endDate]
,[Branches.Branch_Employee].[branchId] AS Branches.Branch_Employee.branchId]
,[Branches.Branch_Employee].[employeeId] AS [Branches.Branch_Employee.employeeId]
FROM [Employee] AS [Employee]
LEFT OUTER JOIN (
    [Branch_Employee] AS [Branches.Branch_Employee]
    INNER JOIN [Branch] AS [Branches] 
    ON [Branches].[IdBranch] = [Branches.Branch_Employee].[branchId]
) ON [Employee].[idEmployee] = [Branches.Branch_Employee].[employeeId]
AND [Branches].[endDate] IS NULL
WHERE [Employee].[active] = 1;

但是，我实际上需要进一步限制结果集，使其仅包含“当前”分支-员工关系，即类似:

[Branches.Branch_Employee].[endDate] IS NULL

Answer 1

我应该对 Sequelize 文档给予更多的信任，因为通常情况下，它有答案。我将其包括在内，以防其他人遇到这个特定问题:

来自 Sequelize doc ，findAll(options) 函数接受以下选项:

[options.include[].through.where] Object Filter on the join model for belongsToMany relations.

这正是我所需要的!希望这会有所帮助。