如何 ORM 续集连接表上的附加列? [英] How do I ORM additional columns on a join table in sequelize?
问题描述
我正在使用 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:
Driver->DriverCarDriverCar->汽车汽车->DriverCarDriverCar->Driver
Driver->DriverCarDriverCar->CarCar->DriverCarDriverCar->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 的正确行为.
Note that there is no secret. Observe that the extra attribute that you're looking for, color, comes nested in the query result, not in the same nesting level of the Car or Driver. This is the correct behavior of Sequelize.
确保您可以运行此代码并获得与我相同的结果.我的 Node 版本不同，但我怀疑这可能与任何事情有关.然后，将我的代码与您的代码进行比较，看看您是否可以找出导致问题的原因.如果您需要进一步的帮助，请随时在评论中提问:)
Make sure you can run this code and get the same result I do. My version of Node is different but I doubt that could be related to anything. Then, compare my code to your code and see if you can figure out what is causing you problems. If you need further help, feel free to ask in a comment :)
由于我偶然发现了这方面的问题，并且这与您的情况有关，我想我应该在我的回答中添加一个部分，提醒您注意建立过于复杂的多对多关系的陷阱"(它是经过一段时间的努力，我学到了自己的教训).
Since I stumbled myself upon problems with this, and this is related to your situation, I thought I should add a section in my answer alerting you to the "trap" of setting up an overcomplicated many-to-many relationship (it's a lesson that I learned myself after struggling for a while).
我不会重复我自己，我只是简单地引用我在 续集Issue 9158，并添加阅读链接:
Instead of repeating myself, I will just add a brief quote of what I said in Sequelize Issue 9158, and add links for further reading:
联结表，即关系数据库中存在的表示多对多关系的表，最初只有两个字段(每个表的外键定义多对多关系).虽然确实可以在该表上定义额外的字段/属性，即关联本身的额外属性(如您在问题标题中输入的那样)，但此处应注意:如果它变得过于复杂，则表明您应该将您的连接表提升"为成熟的实体.
Junction tables, the tables that exist in relational databases to represent many-to-many relationships, initially have only two fields (the foreign keys of each table defining the many-to-many relationship). While it's true that it's possible to define extra fields/properties on that table, i.e. extra properties for the association itself (as you put in the issue title), care should be taken here: if it's getting overcomplicated, it's a sign that you should "promote" your junction table to a full-fledged entity.
进一步阅读:
- 我自己回答的问题涉及到 sequelize 中的多对多关系的过于复杂的设置:FindAll 包括涉及复杂的多对(多对多)关系(sequelizejs)
- 及其同级问题:是否可以在涉及的表之一已经存在的情况下建立多对多关系连接表?
这篇关于如何 ORM 续集连接表上的附加列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
