时间戳

首页 > 解决方案 > 属于许多关联，将外键添加到源模型，SequelizeDatabaseError: column "CategoryID" does not exist

node.js - 属于许多关联，将外键添加到源模型，SequelizeDatabaseError: column "CategoryID" does not exist

问题描述

我试图解释我的情况。我有两个模型：Film和Category. 它们是 N:M 关联。

迁移文件20200123070411-createTables.js：

'use strict';

module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Category', {
      ID: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      Name: {
        type: Sequelize.STRING(20),
        allowNull: false,
      },
      Last_Update: {
        type: Sequelize.DATE,
        allowNull: false,
      },
    });
    await queryInterface.createTable('Language', {
      ID: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      Name: {
        type: Sequelize.STRING(20),
        allowNull: false,
      },
      Last_Update: {
        type: Sequelize.DATE,
        allowNull: false,
      },
    });
    await queryInterface.createTable('Film', {
      ID: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true,
        allowNull: false,
      },
      LanguageID: {
        type: Sequelize.INTEGER,
        references: {
          model: 'Language',
          key: 'ID',
        },
        onDelete: 'restrict',
        allowNull: false,
      },
      Title: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      Description: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      Release_Year: {
        type: Sequelize.INTEGER,
        allowNull: false,
      },
      Rental_Duration: {
        type: Sequelize.INTEGER,
        allowNull: false,
      },
      Rental_Date: {
        type: Sequelize.DECIMAL(19, 0),
        allowNull: false,
      },
      Length: {
        type: Sequelize.INTEGER,
        allowNull: false,
      },
      Replacement_Cost: {
        type: Sequelize.DECIMAL(19, 0),
        allowNull: false,
      },
      Rating: {
        type: Sequelize.INTEGER,
        allowNull: false,
      },
      Last_Update: {
        type: Sequelize.DATE,
        allowNull: false,
      },
      Special_Features: {
        type: Sequelize.STRING,
        allowNull: false,
      },
      Fulltext: {
        type: Sequelize.STRING,
        allowNull: false,
      },
    });

    await queryInterface.createTable(
      'Film_Category',
      {
        FilmID: {
          type: Sequelize.INTEGER,
          // composite primary key
          primaryKey: true,
          references: {
            model: 'Film',
            key: 'ID',
          },
          onDelete: 'restrict',
        },
        CategoryID: {
          type: Sequelize.INTEGER,
          primaryKey: true,
          references: {
            model: 'Category',
            key: 'ID',
          },
          onDelete: 'cascade',
        },
        Last_Update: {
          type: Sequelize.DATE,
          allowNull: false,
        },
      }
    );
  },

  down: async (queryInterface, Sequelize) => {
    await queryInterface.dropTable('Film_Category');
    await queryInterface.dropTable('Film');
    await queryInterface.dropTable('Category');
    await queryInterface.dropTable('Language');
  },
};

执行数据库迁移后，我在下面定义模型：

models/category.ts：

import { Model, DataTypes, BelongsToManyGetAssociationsMixin } from 'sequelize';
import { sequelize } from '../db';
import { Film } from './film_category';

class Category extends Model {
  public ID!: number;
  public Name!: string;
  public Last_Update!: Date;
  public getFilms!: BelongsToManyGetAssociationsMixin<Film>;
}
Category.init(
  {
    ID: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    Name: {
      type: DataTypes.STRING(20),
      allowNull: false,
    },
    Last_Update: {
      type: DataTypes.DATE,
      allowNull: false,
    },
  },
  { sequelize, modelName: 'Category' },
);

export { Category };

models/film.ts：

import { Model, DataTypes, BelongsToManyGetAssociationsMixin } from 'sequelize';
import { sequelize } from '../db';
import { Category } from './film_category';

class Film extends Model {
  public ID!: number;
  public LanguageID!: number;
  public Title!: string;
  public Description!: string;
  public Release_Year!: number;
  public Rental_Duration!: number;
  public Rental_Date!: number;
  public Length!: number;
  public Replacement_Cost!: number;
  public Rating!: number;
  public Last_Update!: Date;
  public Special_Features!: string;
  public Fulltext!: string;
  public getCategories!: BelongsToManyGetAssociationsMixin<Category>;
}
Film.init(
  {
    ID: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    LanguageID: {
      type: DataTypes.INTEGER,
      references: {
        model: 'Language',
        key: 'ID',
      },
      onDelete: 'restrict',
      allowNull: false,
    },
    Title: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    Description: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    Release_Year: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    Rental_Duration: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    Rental_Date: {
      type: DataTypes.DECIMAL(19, 0),
      allowNull: false,
    },
    Length: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    Replacement_Cost: {
      type: DataTypes.DECIMAL(19, 0),
      allowNull: false,
    },
    Rating: {
      type: DataTypes.INTEGER,
      allowNull: false,
    },
    Last_Update: {
      type: DataTypes.DATE,
      allowNull: false,
    },
    Special_Features: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    Fulltext: {
      type: DataTypes.STRING,
      allowNull: false,
    },
  },
  { sequelize, modelName: 'Film' },
);

export { Film };

models/film_category.ts：

import { Model, DataTypes } from 'sequelize';
import { sequelize } from '../db';
import { Category } from './category';
import { Film } from './film';

class FilmCategory extends Model {
  public FilmID!: number;
  public CategoryID!: number;
  public Last_Update!: Date;
}
FilmCategory.init(
  {
    FilmID: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      references: {
        model: 'Film',
        key: 'ID',
      },
      onDelete: 'restrict',
    },
    CategoryID: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      references: {
        model: 'Category',
        key: 'ID',
      },
      onDelete: 'cascade',
    },
    Last_Update: {
      type: DataTypes.DATE,
      allowNull: false,
    },
  },
  { sequelize, modelName: 'Film_Category' },
);

export { FilmCategory, Film, Category };

models/index.ts：

import { Category } from './category';
import { Film } from './film';
import { Language } from './language';
import { FilmCategory } from './film_category';

Category.belongsToMany(Film, { through: FilmCategory });
Film.belongsToMany(Category, { through: FilmCategory });

Language.hasMany(Film);

export { Category, Film, Language, FilmCategory };

当我尝试调用Film.findByPk(1)时，sequelize抛出一个错误：

SequelizeDatabaseError：列“CategoryID”不存在

sequelizeof生成的 SQL 查询Film.findByPk(1)如下：

执行（默认）：选择“ID”、“LanguageID”、“Title”、“Description”、“Release_Year”、“Rental_Duration”、“Rental_Date”、“Length”、“Replacement_Cost”、“Rating”、“Last_Update”、 "Special_Features", "Fulltext", "CategoryID" FROM "Film" AS "Film" WHERE "Film"."ID" = 1;

我知道当我使用时Film.belongsToMany(Category, { through: FilmCategory });，sequelize会将CategoryID目标模型添加Category到源模型Film中。我希望通过主键找到的电影数据具有与模型模式相同的属性。这个额外的CategoryID专栏是问题所在。

因此，我不希望将此CategoryID列添加到Film模型上，而FilmID将列添加到Category模型上。因为Film表没有CategoryID列并且表在数据库Category中没有列。FilmID它们由连接表连接Film_Category。有没有办法做到这一点？或者，我错过了什么？

创建了一个最小的可重现代码仓库：https ://github.com/mrdulin/node-sequelize-examples/tree/master/src/db

标签： node.jspostgresqltypescriptsequelize.js

解决方案

如果您在关联中明确定义“通过”表，应该可以工作，如下所示：

    Film.belongsToMany(Category, 
        {
         through: FilmCategory, 
         foreignKey: 'FilmID',  
         otherKey: 'CategoryID' 
        });

可能会出现问题，因为您使用的是 ID 而不是 Id，但这只是猜测......

高温高压

推荐阅读