title: Sequelize: Including join table attributes in findAll include
body: <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>