Sequelize: how to do a WHERE condition on joined table with left outer join

Question

My database model is as follows:
An employee drives one or zero vehicles
A vehicle can be driven by one or more employees
A vehicle has a model type that tells us it's fuel type amongst other things.

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.
So where VehicleID is null OR Vehicle.VehicleModel.IsDiesel = false

My current code is as follows:

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

If I run the following:

options.include = [{     model: model.Vehicle,     attributes: ['ID', 'ModelID'],         include: [         {             model: model.VehicleModel,             attributes: ['ID', 'IsDiesel']         }] }];  employee  .findAll(options)  .success(function(results) {      // do stuff  }); 

Sequelize does a left outer join to get me the included tables. So I get employees who drive vehicles and who don't.
As soon as I add a where to my options:

options.include = [{     model: model.Vehicle,     attributes: ['ID', 'ModelID'],     include: [         {             model: model.VehicleModel,             attributes: ['ID', 'IsDiesel']             where: {                 IsDiesel: false             }         }] }]; 

Sequelize now does an inner join to get the included tables.
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.

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.

EDIT:

It turns out that the solution was to use required: false, as below:

options.include = [{     model: model.Vehicle,     attributes: ['ID', 'ModelID'],     include: [         {             model: model.VehicleModel,             attributes: ['ID', 'IsDiesel']             where: {                 IsDiesel: false             },             required: false         }],     required: false  }]; 

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.

Answer 1

When you add a where clause, sequelize automatically adds a required: true clause to your code.

Adding required: false to your include segment should solve the problem

Note: you should check this issue iss4019

Answer 2

Eager loading

When you are retrieving data from the database there is a fair chance that you also want to get associations with the same query - this is called eager loading. The basic idea behind that, is the use of the attribute include when you are calling find or findAll. when you set

required: false

will do

LEFT OUTER JOIN 

when

required: true

will do

INNER JOIN 

for more detail docs.sequelizejs eager-loading