如何在sequelize中对连接表上的其他列进行ORM？

本文介绍了如何在sequelize中对连接表上的其他列进行ORM？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我正在使用节点v9.5，续集v4.33（postgres方言）。I'm using node v9.5, sequelize v4.33 (postgres dialect).我有两个一流的模型：驱动程序（特定人员）和汽车（通用品牌+型号组合）。到目前为止，它们已通过多对多连接表连接。现在我想开始跟踪该连接表上的其他属性，但是在声明这些关系时遇到了问题，因此它们确实有效。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 simplerDriver.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（）时，不会从连接表中获取/ code>属性。 （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 到 DriverCar ：So, instead, I tried spelling out this relationship more explicitly, by associating Driver to DriverCar, and then Car to DriverCar:// Driver -> CarDriver.hasMany(DriverCar, { as: 'carList', foreignKey: 'driverId'})// Car -> DriverCar.hasMany(DriverCar, { foreignKey: 'carId'})我还告诉sqlz DriverCar 没有标准的行ID：I also tell sqlz that DriverCar won't have a standard row id:DriverCar.removeAttribute('id')此时，请求驱动程序的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 DriverCarSET "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 - > DriverCar DriverCar - > 汽车 汽车 - > DriverCar DriverCar - > 驱动程序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 aliasesBefore 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（）等，这些更多nse to me。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. I已经制作了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 或驱动程序。这是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).我不会重复自己，而是只需添加我在 Sequelize 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: 进一步阅读： 我自己回答的问题涉及续集中多对多关系的过于复杂的设置： FindAll包括涉及复杂的多对多（多对多）关系（sequelizejs） 及其兄弟问题：是否可以建立多对多关系，其中一个表已经是联结表？My own self-answered question involving an overcomplicated setup of many-to-many relationships in sequelize: FindAll with includes involving a complicated many-to-(many-to-many) relationship (sequelizejs)And its sibling question: Is it OK to have a many-to-many relationship where one of the tables involved is already a junction table? 这篇关于如何在sequelize中对连接表上的其他列进行ORM？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！