Steve Steve - 2 months ago 36
Node.js Question

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

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.

Picture of database diagram

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

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

Comments