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.
我想在他们不开车的情况下将所有员工带回给我,或者如果他们开车,那么车辆不是柴油车.
因此,VehicleID 为 null 或 Vehicle.VehicleModel.IsDiesel = false
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
我目前的代码如下:
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);
如果我运行以下命令:
options.include = [{
model: model.Vehicle,
attributes: ['ID', 'ModelID'],
include: [
{
model: model.VehicleModel,
attributes: ['ID', 'IsDiesel']
}]
}];
employee
.findAll(options)
.success(function(results) {
// do stuff
});
Sequelize 执行左外连接以获取包含的表.所以我找到了会开车和不开车的员工.
只要我在选项中添加位置:
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 现在执行内部连接以获取包含的表.
这意味着我只聘请驾驶车辆且车辆不是柴油的员工.不开车的员工被排除在外.
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.
从根本上说,我需要一种方法来告诉 Sequelize 进行左外连接,同时有一个 where 条件表明连接表中的列是 false 或 null.
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.
原来解决办法是使用required:false,如下:
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
}];
我已经尝试过放置第一个 'required:false' 但我错过了放置内部的.我认为它不起作用,所以我放弃了这种方法.Dajalmar Gutierrez 的回答让我意识到我需要两者才能正常工作.
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.
推荐答案
添加where子句时,sequelize会自动添加required: true
代码中的子句.
When you add a where clause, sequelize automatically adds a required: true
clause to your code.
将 required: false
添加到包含段应该可以解决问题
Adding required: false
to your include segment should solve the problem
注意:你应该检查这个问题 iss4019
Note: you should check this issue iss4019
相关文章