Sequelize: how to fulfill the WHERE clause on a joined table with a left outer join

Question

My database model is as follows:
Employee drives one or zero car
The car can be driven by one or more employees.
A vehicle has a type of model that tells us that it is a type of fuel among other things.

I would like for me to bring all the employees where they did not drive the car, or if they do, the car is not diesel. So where is VehicleID 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 performs a left outer join to get the included tables. Therefore, I get employees who drive vehicles and who do not.
As soon as I add where my options are:

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

Sequelize now internally joins to get included tables.
This means that I only get employees who drive the vehicle, and the vehicle is not diesel. Employees who do not drive a vehicle are excluded.

Basically, I need to tell Sequelize to execute the left outer join and at the same time have a where clause that indicates that the column from the joined table is false or null.

EDIT:

It turns out that a solution should have been required: false, as shown below:

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

I already tried to put the first "mandatory: false", but I missed its internal. I thought this did not work, so I abandoned this approach. The answer of Dajjalmar Gutierrez made me understand what I need both for work and for work.

Answer 1

When you add a where clause, sequelize automatically adds the 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

Unwanted loading

When you retrieve data from a database, there is a possibility that you will also want to get associations with the same query - this is called looking forward . The basic idea behind this is that using an attribute includes when you call find or findAll. when you install

required: false

will make

 LEFT OUTER JOIN 

when

required: true

will make

 INNER JOIN 

for more details docs.sequelizejs eager-loading