Sequelize: how to do a WHERE condition on joined table with left outer join
<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>