Sequelize: Including join table attributes in findAll include
<p>I'm currently working on an app for managing branches and employees in a company, using NodeJS, MSSQL, and Sequilize as the ORM. </p>

<p>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 (<code>initDate</code> and <code>endDate</code>) 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 <code>endDate</code> to <code>now()</code> and create a new record with the change and <code>endDate = null</code>, so the 'current' version of every branch or branch-employee relation is the one with <code>endDate IS NULL</code>.</p>

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

<p>Thanks a lot for your help. Any insight would be really appreciated!</p>

<p>--- Here I include the model definitions and the query ---</p>

<p>Branch model definition:</p>

<pre><code>var BranchModel = db.define&lt;BranchInstance, Branch&gt;('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 ..

        }
    }
});
</code></pre>

<p>Employee model definition:</p>

<pre><code>var EmployeeModel = db.define&lt;EmployeeInstance, Employee&gt;('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 ...
        }
    }
});
</code></pre>

<p>Join table definition:</p>

<pre><code>var Branch_Employee = db.define&lt;BranchEmployeeInstance, BranchEmployee&gt;('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 ...
    }
});
</code></pre>

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

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

<p>The generated SQL for the above query, looks like this:</p>

<pre><code>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;
</code></pre>

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

<pre><code>[Branches.Branch_Employee].[endDate] IS NULL
</code></pre>