Sequelize:如何使用左外连接对连接表执行 WHERE 条件 [英] 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.

我想在他们不开车的情况下将所有员工带回给我，或者如果他们开车，那么车辆不是柴油车.
因此，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

这篇关于Sequelize:如何使用左外连接对连接表执行 WHERE 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！