title: Sequelize: how to do a WHERE condition on joined table with left outer join
body: <p>My database model is as follows:<br>
  An employee drives one or zero vehicles<br>
  A vehicle can be driven by one or more employees<br>
  A vehicle has a model type that tells us it's fuel type amongst other things.</p>
  
  <p><a href="https://i.stack.imgur.com/VCKQY.png" rel="noreferrer"><img src="https://i.stack.imgur.com/VCKQY.png" alt="Picture of database diagram"></a></p>
  
  <p>I'd like sequelize to fetch me all employees where they don't drive a vehicle, or if they do then the vehicle is not diesel.<br>
  So where VehicleID is null OR Vehicle.VehicleModel.IsDiesel = false  </p>
  
  <p>My current code is as follows:</p>
  
  <pre><code>var employee = sequelize.define('employee', {
      ID: Sequelize.INTEGER,
      VehicleID: Sequelize.INTEGER
  });
  
  var vehicle = sequelize.define('vehicle', {
      ID: Sequelize.INTEGER,
      ModelID: Sequelize.INTEGER
  });
  
  var vehicleModel = sequelize.define('vehicleModel', {
      ID: Sequelize.INTEGER,
      IsDiesel: Sequelize.BOOLEAN
  });
  
  employee.belongsTo(vehicle);
  vehicle.belongsTo(vehicleModel);
  </code></pre>
  
  <p>If I run the following:</p>
  
  <pre><code>options.include = [{
      model: model.Vehicle,
      attributes: ['ID', 'ModelID'],
          include: [
          {
              model: model.VehicleModel,
              attributes: ['ID', 'IsDiesel']
          }]
  }];
  
  employee
   .findAll(options)
   .success(function(results) {
       // do stuff
   });
  </code></pre>
  
  <p>Sequelize does a left outer join to get me the included tables. So I get employees who drive vehicles and who don't.<br>
  As soon as I add a where to my options:</p>
  
  <pre><code>options.include = [{
      model: model.Vehicle,
      attributes: ['ID', 'ModelID'],
      include: [
          {
              model: model.VehicleModel,
              attributes: ['ID', 'IsDiesel']
              where: {
                  IsDiesel: false
              }
          }]
  }];
  </code></pre>
  
  <p>Sequelize now does an inner join to get the included tables.<br>
  This means that I only get employees who drive a vehicle and the vehicle is not diesel. The employees who don't drive a vehicle are excluded.</p>
  
  <p>Fundamentally, I need a way of telling Sequelize to do a left outer join and at the same time have a where condition that states the column from the joined table is false or null.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>It turns out that the solution was to use required: false, as below:</p>
  
  <pre><code>options.include = [{
      model: model.Vehicle,
      attributes: ['ID', 'ModelID'],
      include: [
          {
              model: model.VehicleModel,
              attributes: ['ID', 'IsDiesel']
              where: {
                  IsDiesel: false
              },
              required: false
          }],
      required: false
  
  }];
  </code></pre>
  
  <p>I had already tried putting the first 'required:false' but I missed out on putting the inner one. I thought it wasn't working so I gave up on that approach. Dajalmar Gutierrez's answer made me realise I needed both for it to work.</p>