sequelize多条件_Sequelize：如何使用左外连接对连接表执行WHERE条件

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.

解决方案

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