Postgres：SequelizeDatabseError：列 ID 不存在

Question

我在 Node.JS 项目中使用 Postgresql 和代码优先方法。一些表已经存在于继承的代码中。有一个名为 user_games 的表，存储有关哪个用户正在玩哪些游戏的信息。表的模型定义如下：

'use strict';
const {
  Model
} = require( 'sequelize' );
module.exports = ( sequelize, DataTypes ) => {
  class UserGames extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate( models ) {
      // define association here
      UserGames.belongsTo( models.Game, {
        as: 'Games',
        foreignKey: 'game_id',
        onDelete: 'NO ACTION',
        onUpdate: 'NO ACTION'
      } );

      UserGames.belongsTo( models.User, {
        as: 'User',
        foreignKey: 'user_id',
        onDelete: 'NO ACTION',
        onUpdate: 'NO ACTION'
      } );

      UserGames.hasMany( models.GameUrl, {
        foreignKey: 'game_id'
      } );
    }
  }

  UserGames.init( {
    user_game_id: { // eslint-disable-line camelcase
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true,
      allowNull: false
    },
    user_id: DataTypes.STRING, // eslint-disable-line camelcase
    games_gamer_id: DataTypes.STRING, // eslint-disable-line camelcase
    game_id: DataTypes.UUID // eslint-disable-line camelcase
  }, {
    sequelize,
    createdAt: 'createdAt',
    updatedAt: 'updatedAt',
    modelName: 'UserGames',
    tableName: 'user_games',
  } );
  return UserGames;
};

一切工作正常，但突然以下（和其他相关）代码开始抱怨“列 UserGames.id 不存在”：

models.Game.findAndCountAll( {
include: [ {
  model: models.UserGames,
  as: 'UserGames',
  where: { user_id: userId }, // eslint-disable-line camelcase
} ],
attributes: [ 'name', 'description_text', 'icon_url' ],
order: [
  [ 'name', 'ASC' ]
],

} );

因此，我们必须修改表 user_games 的“包含”部分，以显式指定属性，如下所示：

include: [ {
  model: models.UserGames,
  as: 'UserGames',
  where: { user_id: userId }, // eslint-disable-line camelcase
  attributes: [ 'game_id' ], //otherwise it will look for 'id' field also.
} ],

如模型定义所示，该表包含列 user_game_id 作为主键，正如我所提到的，事情运行良好几天前。所以，我无法理解错误的原因。任何建议将受到高度赞赏。

编辑：这是游戏模型：

/* eslint-disable camelcase */
'use strict';
const {
  Model
} = require( 'sequelize' );
module.exports = ( sequelize, DataTypes ) => {
  class Game extends Model {
    /**
     * Helper method for defining associations.
     * This method is not a part of Sequelize lifecycle.
     * The `models/index` file will call this method automatically.
     */
    static associate( models ) {
      // define association here
      Game.belongsTo( models.Partner, {
        as: 'Partner',
        foreignKey: 'partner_id',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
      } );
      Game.hasMany( models.UserGames, {
        as: 'UserGames',
        foreignKey: 'game_id',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
      } );
      Game.hasMany( models.GameAsset, {
        as: 'GameAssetDetails',
        foreignKey: 'game_id',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
      } );
      Game.hasMany( models.GameUrl, {
        as: 'GameUrlDetails',
        foreignKey: 'game_id',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
      } );
      Game.hasMany( models.GameTestingComment, {
        foreignKey: 'game_id',
        onDelete: 'CASCADE',
        onUpdate: 'CASCADE'
      } );
      Game.hasMany( models.OfferSchedule, {
        foreignKey: 'game_id'
      } );
    }
  }

  Game.init( {
    game_id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      primaryKey: true,
      allowNull: false
    },
    partner_id: {
      type: DataTypes.UUID,
      allowNull: false
    },
    name: DataTypes.STRING,
    description: DataTypes.TEXT,
    description_text: DataTypes.TEXT,
    icon_url: DataTypes.STRING,
    status: {
      type: DataTypes.ENUM,
      values: [ "1", "2", "3", "4" ],
      defaultValue: "1",
      comment: "1 => 'In Development', 2 => 'Submit for Approval', 3 => 'Approved', 4 => 'Rejected'",
    },
    approved_date: {
      allowNull: true,
      type: DataTypes.DATE
    },
    submitted_date: {
      allowNull: true,
      type: DataTypes.DATE
    },
    is_active: {
      type: DataTypes.BOOLEAN,
      defaultValue: true,
      allowNull: false
    }
  }, {
    sequelize,
    createdAt: 'createdAt',
    updatedAt: 'updatedAt',
    modelName: 'Game',
    tableName: 'game',
  } );
  return Game;
};

Answer 1

最终，我们能够找出问题的根源。几天前，团队中的一位开发人员添加了错误的关联。他将 UserGames 的“id”列设置为其他表中的外键，尽管 UserGames 根本没有“id”列。下面是错误代码：

static associate( models ) {
  GameProducts.hasMany( models.UserGames, {
    as: 'SubscriptionPlans',
    sourceKey: "game_product_id",
    foreignKey: 'id' //Faulty code. UserGames does not have 'id' column
  } );
}