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为空或车辆。 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做一个左外连接，同时有一个地方声明连接表中的列为false或为空的条件。

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

}];

我已经尝试过把第一个'必需：假'但我错过了放内部的。我认为它不起作用所以我放弃了这种方法。 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.

推荐答案

当你添加一个的地方子句，sequelize会自动为您的代码添加 required：true 子句。

When you add a where clause, sequelize automatically adds a required: true clause to your code.

添加 所需：false 到您的包含段应该可以解决问题

Adding required: false to your include segment should solve the problem

注意：你应该检查此问题 iss4019

Note: you should check this issue iss4019

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