问题描述

我正在使用 node v9.5，sequelize v4.33(postgres 方言).

I'm using node v9.5, sequelize v4.33 (postgres dialect).

我有两个一流的模型:Driver(特定人)和Car(通用品牌+模型组合).到目前为止，它们已通过多对多连接表连接起来.现在我想开始跟踪该联接表上的其他属性，但在声明这些关系以便它们实际工作时遇到了麻烦.

I have two first-class models: Driver (specific people) and Car (generic make+model combinations). Thus far, they've been connected by a many-to-many join table. Now I want to start tracking additional properties on that join table, but am having trouble declaring these relationships so they actually work.

const Driver = sqlz.define('Driver', {
    id: { primaryKey: true, type: DataTypes.UUID },
    name: DataTypes.string
})

const Car = sqlz.define('Car', {
    id: { primaryKey: true, type: DataTypes.UUID },
    make: DataTypes.string,
    model: DataTypes.string
})

// old associations; worked great when requirements were simpler
Driver.belongsToMany(Car, {
    through: 'DriverCar',
    as: 'carList',
    foreignKey: 'driverId'
})

Car.belongsToMany(Driver, {
    through: 'DriverCar',
    as: 'driverList',
    foreignKey: 'carId'
})

现在我想开始跟踪有关汽车与其驾驶员之间关系的更多信息，例如该特定汽车的颜色.

Now I want to begin tracking more information about the relationship between a car and its driver, like the color of that specific car.

第 1 步:我更新迁移脚本，向连接表添加一个新列，如下所示:

Step 1: I update the migration script, adding a new column to the join table like so:

queryInterface.createTable( 'DriverCar', {
    driverId: {
        type: sqlz.UUID,
        allowNull: false,
        primaryKey: true,
        references: {
            model: 'Driver',
            key: 'id'
        }
    },
    carId: {
        type: sqlz.UUID,
        allowNull: false,
        primaryKey: true,
        references: {
            model: 'Car',
            key: 'id'
        }
    },
    createdAt: {
        type: sqlz.DATE,
        allowNull: false
    },
    updatedAt: {
        type: sqlz.DATE,
        allowNull: false
    },

    // new column for join table
    color: {
      type: Sequelize.STRING
    }
})

第 2 步:我为 DriverCar 定义了一个新的 sqlz 模型:

Step 2: I define a new sqlz model for DriverCar:

const DriverCar = sqlz.define('DriverCar', {
    color: DataTypes.string
})

(我假设我只需要定义有趣的属性，并且仍然会从将要定义的关联中推断出 driverId 和 carId.)

(I assume I only need to define the interesting properties, and that driverId and carId will still be inferred from the associations that will be defined.)

第 3 步:我需要更新 Driver、Car 和 DriverCar 之间存在的关联.

Step 3: I need to update the associations that exist among Driver, Car, and DriverCar.

这就是我卡住的地方.我已尝试更新现有关联，如下所示:

This is where I'm stuck. I have attempted updating the existing associations, like so:

Driver.belongsToMany(Car, {
    through: DriverCar, // NOTE: no longer a string, but a reference to new DriverCar model
    as: 'carList',
    foreignKey: 'driverId'
})

Car.belongsToMany(Driver, {
    through: DriverCar, // NOTE: no longer a string, but a reference to new DriverCar model
    as: 'driverList',
    foreignKey: 'carId'
})

这执行没有错误，但是当我尝试 driver.getCarList() 时，新的 color 属性没有从连接表中获取.(Sqlz 被配置为记录每个 SQL 语句，并且我已经验证没有请求连接表中的属性.)

This executes without error, but the new color property is not fetched from the join table when I try driver.getCarList(). (Sqlz is configured to log every SQL statement, and I have verified that no properties from the join table are being requested.)

因此，我尝试更明确地说明这种关系，方法是将 Driver 关联到 DriverCar，然后将 Car 关联到 司机车:

So, instead, I tried spelling out this relationship more explicitly, by associating Driver to DriverCar, and then Car to DriverCar:

// Driver -> Car
Driver.hasMany(DriverCar, {
    as: 'carList',
    foreignKey: 'driverId'
})

// Car -> Driver
Car.hasMany(DriverCar, {
    foreignKey: 'carId'
})

我还告诉 sqlz DriverCar 不会有标准的行 id:

I also tell sqlz that DriverCar won't have a standard row id:

DriverCar.removeAttribute('id')

此时，请求 Driver 的 carList (driver.getCarList()) 似乎可行，因为我可以看到在 SQL 中获取连接表道具.但保存失败:

At this point, requesting a Driver's carList (driver.getCarList()) seems to work, because I can see join table props being fetched in SQL. But saving fails:

driverModel.setCarList([ carModel1 ])

UPDATE DriverCar
SET "driverId"='a-uuid',"updatedAt"='2018-02-23 22:01:02.126 +00:00'
WHERE "undefined" in (NULL)

错误:

SequelizeDatabaseError: column "undefined" does not exist

我认为发生此错误是因为 sqzl 不了解识别连接表中行的正确方法，因为我未能建立必要的关联.坦率地说，我不确定我是否正确地做到了这一点.我是 ORM 的新手，但我希望我需要指定 4 个关联:

I assume this error is occurring because sqzl doesn't understand the proper way to identify rows in the join table, because I've failed to establish the necessary associations. And frankly, I'm not confident I've done this correctly; I'm new to ORMs, but I was expecting I'd need to specify 4 assocations:

1. Driver -> DriverCar
2. DriverCar -> 汽车
3. 汽车 -> DriverCar
4. DriverCar -> Driver

1. Driver -> DriverCar
2. DriverCar -> Car
3. Car -> DriverCar
4. DriverCar -> Driver

<小时>

回顾一下:我有 2 个一流的实体，以多对多的关系加入.我正在尝试将数据添加到关系中，发现 ORM 需要以不同方式定义这些关联，并且在阐明新关联时遇到了麻烦.

To recap: I have 2 first-class entities, joined in a many-to-many relationship. I'm trying to add data to the relationship, have discovered that the ORM requires defining those associations differently, and am having trouble articulating the new associations.

推荐答案

关于别名的说明

在回答之前，我想指出您选择的别名(carList 和 driverList)可能会更好，因为虽然自动生成的 sequelize方法 .setCarList() 和 .setDriverList() 确实有意义，方法 .addCarList(), .addDriverList()、.removeCarList() 和 .removeDriverList() 都是无稽之谈，因为它们只将单个实例作为参数，而不是列表.

A note about your aliases

Before going to the answer, I would like to point out that your choice of aliases (carList and driverList) could be better, because although the auto-generated sequelize methods .setCarList() and .setDriverList() do make sense, the methods .addCarList(), .addDriverList(), .removeCarList() and .removeDriverList() are nonsense, since they take only a single instance as a parameter, not a list.

对于我的回答，我不会使用任何别名，而让 Sequelize 默认为 .setCars(), .setDrivers(), .addCar()、.removeCar() 等，这对我来说更有意义.

For my answer, I won't use any aliases, and let Sequelize default to .setCars(), .setDrivers(), .addCar(), .removeCar(), etc, which make much more sense to me.

我制作了一个 100% 独立的代码来测试这一点.只需复制粘贴并运行它(在运行 npm install sequelize sqlite3 之后):

I've made a 100% self-contained code to test this. Just copy-paste it and run it (after running npm install sequelize sqlite3):

const Sequelize = require("sequelize");
const sequelize = new Sequelize({ dialect: 'sqlite', storage: 'db.sqlite' });

const Driver = sequelize.define("Driver", {
    name: Sequelize.STRING
});
const Car = sequelize.define("Car", {
    make: Sequelize.STRING,
    model: Sequelize.STRING
});
const DriverCar = sequelize.define("DriverCar", {
    color: Sequelize.STRING
});
Driver.belongsToMany(Car, { through: DriverCar, foreignKey: "driverId" });
Car.belongsToMany(Driver, { through: DriverCar, foreignKey: "carId" });

var car, driver;

sequelize.sync({ force: true })
    .then(() => {
        // Create a driver
        return Driver.create({ name: "name test" });
    })
    .then(created => {
        // Store the driver created above in the 'driver' variable
        driver = created;

        // Create a car
        return Car.create({ make: "make test", model: "model test" });
    })
    .then(created => {
        // Store the car created above in the 'car' variable
        car = created;

        // Now we want to define that car is related to driver.
        // Option 1:
        return car.addDriver(driver, { through: { color: "black" }});

        // Option 2:
        // return driver.setCars([car], { through: { color: "black" }});

        // Option 3:
        // return DriverCar.create({
        //     driverId: driver.id,
        //     carId: car.id,
        //     color: "black"
        // });
    })
    .then(() => {
        // Now we get the things back from the DB.

        // This works:
        return Driver.findAll({ include: [Car] });

        // This also works:
        // return car.getDrivers();

        // This also works:
        // return driver.getCars();
    })
    .then(result => {
        // Log the query result in a readable way
        console.log(JSON.stringify(result.map(x => x.toJSON()), null, 4));
    });

上面的代码按预期记录(至少如我所料):

The code above logs as expected (as I would expect, at least):

[
    {
        "id": 1,
        "name": "name test",
        "createdAt": "2018-03-11T03:04:28.657Z",
        "updatedAt": "2018-03-11T03:04:28.657Z",
        "Cars": [
            {
                "id": 1,
                "make": "make test",
                "model": "model test",
                "createdAt": "2018-03-11T03:04:28.802Z",
                "updatedAt": "2018-03-11T03:04:28.802Z",
                "DriverCar": {
                    "color": "black",
                    "createdAt": "2018-03-11T03:04:28.961Z",
                    "updatedAt": "2018-03-11T03:04:28.961Z",
                    "driverId": 1,
                    "carId": 1
                }
            }
        ]
    }
]

请注意，没有秘密.请注意，您要查找的额外属性 color 嵌套在查询结果中，而不是在 Car 或 Driver<的同一嵌套级别中/代码>.这是 Sequelize 的正确行为.