Sequelize: Including join table attributes in findAll include

Question

I'm currently working on an app for managing branches and employees in a company, using NodeJS, MSSQL, and Sequilize as the ORM.

A requirement is to keep track of certain changes in a way that we can 'go back' to a date in the past and see the state of the company at that specific point in time. In order to do that, we are using time stamps (initDate and endDate) to track the changes we care about, i.e. changes to the branches, and movement of employees among the active branches. Every time a record is changed, we set its endDate to now() and create a new record with the change and endDate = null, so the 'current' version of every branch or branch-employee relation is the one with endDate IS NULL.

The problem we are facing right now with Sequelize is: How can we specify the endDate for the branch-employee relation when findAll()ing our employees, while eager-loading (i.e. includeing) their branches? In the example below I'm querying the 'current' state of the company, so i'm asking for endDate: null, but that's actually an argument saying the point in time we want.

Thanks a lot for your help. Any insight would be really appreciated!

--- Here I include the model definitions and the query ---

Branch model definition:

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 ..

        }
    }
});

Employee model definition:

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

Join table definition:

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

Sequelize query to get every active employee, eager loading the branches to which they are related:

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

The generated SQL for the above query, looks like this:

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;

But, I actually need to further restrict the result set to only include the 'current' Branch-Employee relations, i.e. something like:

[Branches.Branch_Employee].[endDate] IS NULL

Answer 1

I should have given more credit to the Sequelize docs since, as is often the case, it had the answer. I include it in case someone else is struggling with this particular problem:

From the Sequelize doc, the findAll(options) function accepts the following option:

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

Which is exactly what I needed! Hope this helps.