时间戳

首页 > 解决方案 > 使用 sequelize 在 2 个现有表之间创建关联

javascript - 使用 sequelize 在 2 个现有表之间创建关联

问题描述

我有 2 个现有（用户和 PaymentPlan）表，它们之间没有关联。

PaymentPlan.ts

import { DataTypes, Model } from "sequelize";
import { sequelize } from "./DBConnections/SequelizeNewConnection";

export class PaymentPlan extends Model{
  public ID: number;
  public months: number;
  public fees: number;
  public name: string;
  
  public readonly createdAt!: Date;
  public readonly updatedAt!: Date;
}

PaymentPlan.init(
  {
    ID: {
      type: DataTypes.NUMBER,
      primaryKey: true,
      allowNull: false,
      autoIncrement: true,
    },
    months: { type: DataTypes.NUMBER },
    name: { type: DataTypes.STRING },
  },
  {
    tableName: "paymentplans",
    sequelize,
  },
);

用户.ts

import { PaymentPlan } from "./PaymentPlan";
import { Model, DataTypes } from "sequelize";
import { sequelize } from "./DBConnections/SequelizeNewConnection";
    export class User extends Model{
      public ID: number;
      public name: string;
      public amount: number;
      public fees: number;
      public paymentPlan: number;
        
      public readonly createdAt!: Date;
      public readonly updatedAt!: Date;
    
    }
        User.init(
          {
            ID: {
              type: DataTypes.NUMBER,
              primaryKey: true,
              allowNull: false,
              autoIncrement: true,
            },
            name: { type: DataTypes.STRING },
            amount: { type: DataTypes.NUMBER },
            fees: { type: DataTypes.NUMBER },
            paymentPlan: { type: DataTypes.INTEGER },
          },
          {
            tableName: "users",
            sequelize,
          },
        );

我想在这两个表之间添加一对多关系，所以我添加到 User.ts

PaymentPlan.hasMany(User, {
  foreignKey: 'paymentPlan'
});
User.belongsTo(PaymentPlan);

我运行了查询

ALTER TABLE users ADD CONSTRAINT users_FK_1 FOREIGN KEY (paymentPlan) REFERENCES paymentplans(ID) ON DELETE RESTRICT ON UPDATE CASCADE;

完成所有操作以将 User 表中的 paymentPlan 列与 PaymentPlan 表链接起来。现在当我跑步时

await Users.findAll()

那么正在执行的查询是

SELECT `ID`, `name`, `amount`, `fees`, `paymentPlan`,`createdAt`, `updatedAt`, `PaymentPlanID `FROM `users` AS `User`;

这当然会引发错误：

[SequelizeDatabaseError]: Unknown column 'PaymentPlanID' in 'field list'

因为我没有创建 PaymentPlanID 列。我将外键列指定为 paymentPlan。我究竟做错了什么？如何强制续集将外键列设置为“paymentPlan”而不是创建列“PaymentPlanID”

标签： javascriptmysqlnode.jstypescriptsequelize.js

解决方案

您需要指出与belongsTo您已经在中所做的相同的外键hasMany：

PaymentPlan.hasMany(User, {
  foreignKey: 'paymentPlan'
});
User.belongsTo(PaymentPlan, {
  foreignKey: 'paymentPlan'
});

推荐阅读