表示链接两个模型的关联表

Question

我是NodeJs领域的新手，我正在使用Sequelize with PostgreSQL进行ExpressJS项目。 在寻找表示两个模型之间的关联表的正确方法时，我遇到了一些问题。 这是我的两个表格：

"玩家"：

• 编号
• 名
• 姓氏
• year_of_birth
• 技能
• 登录
• 密码
• created_at
• updated_at

"团队"：

• 编号
• 名字
• created_at
• updated_at

和关联表"team_players"：

• 编号
• id_team
• id_player

在互联网上给出了一些例子，我尝试了以下语法：

Player.belongsToMany(Team, {foreignKey: 'id_player', through: 'team_players'});

当我尝试通过这样的关联访问数据时：

Player.findAll({ include :[{model: Team,foreignKey: 'id_team', through: 'team_players'}] })
. then(results)[...]

它不起作用。

以下是我完成的模型定义。

播放器模型定义：

const { Sequelize } = require('../config/postgres');
const { sequelizeInstance } = require('../config/postgres');
const { Model } = Sequelize;
const Team = require('./Team');
class Player extends Model {}
Player.init({
firstname: {
type: Sequelize.STRING,
allowNull: false,
validate: {isAlpha: true}
},
lastname: {
type: Sequelize.STRING,
allowNull: false,
validate: {isAlpha: true}
},
year_of_birth: {
type: Sequelize.INTEGER,
allowNull: false,
validate: {
min: 1950,
max: 2019,
isNumeric: true
},
},
skills: {
type: Sequelize.INTEGER,
allowNull: false,
validate: {isInt: true}
},
login: {
type: Sequelize.STRING,
allowNull: false,
validate: 
{
isAlphanumeric: true,
unique: true
}
},
password: {
type: Sequelize.STRING,
allowNull: false,
validate: {isAlphanumeric: true}
},
},
{
sequelize: sequelizeInstance,
timestamps: true,
modelName: 'player',
tableName: 'players'
});
Player.belongsToMany(Team, {foreignKey: 'id_player', through: 'team_players'});
module.exports = Player;

团队模型定义：

const { Sequelize } = require('../config/postgres');
const { sequelizeInstance } = require('../config/postgres');
const { Model } = Sequelize;
const Player = require('./Player');
class Team extends Model {}
Team.init({
name: {
type: Sequelize.STRING,
allowNull: false,
unique: true
},
},
{
sequelize: sequelizeInstance,
timestamps: true,
modelName: 'team',
tableName: 'teams'
});
module.exports = Team;

这是错误消息：

name: 'SequelizeDatabaseError',
parent:
{ error: column teams->team_players.teamId does not exist
at Connection.parseE (/home/nku/sources/.perso/pasanga/server/node_modules/pg/lib/connection.js:602:11)
at Connection.parseMessage (/home/nku/sources/.perso/pasanga/server/node_modules/pg/lib/connection.js:399:19)
at Socket.<anonymous> (/home/nku/sources/.perso/pasanga/server/node_modules/pg/lib/connection.js:121:22)
at Socket.emit (events.js:197:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:150:17)
name: 'error',
length: 125,
severity: 'ERROR',
code: '42703',
detail: undefined,
hint: undefined,
position: '776',
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file: 'parse_relation.c',
line: '3293',
routine: 'errorMissingColumn',
sql:
'SELECT "player"."id", "player"."firstname", "player"."lastname", "player"."yob", "player"."skills", "player"."login", "player"."password", "player"."created_at", "player"."updated_at", "teams"."id" AS "teams.id", "teams"."name" AS "teams.name", "teams"."created_at" AS "teams.created_at", "teams"."updated_at" AS "teams.updated_at", "teams->team_players"."created_at" AS "teams.team_players.created_at", "teams->team_players"."updated_at" AS "teams.team_players.updated_at", "teams->team_players"."id_player" AS "teams.team_players.id_player", "teams->team_players"."teamId" AS "teams.team_players.teamId" FROM "players" AS "player" LEFT OUTER JOIN ( "team_players" AS "teams->team_players" INNER JOIN "teams" AS "teams" ON "teams"."id" = "teams->team_players"."teamId") ON "player"."id" = "teams->team_players"."id_player";' }

Answer 1

您从玩家到团队的关联是可以的。 我认为您可以通过将team_players.id_team重命名为默认名称来解决问题team_players.teamId

或者，您可以添加从团队到玩家的关联，以便处理您选择的外键，如下所示：

Team.belongsToMany (Player,{foreignKey: 'id_team', through: 'team_players'});

6月5日编辑

以下是一些工作代码：

const Sequelize = require('sequelize');
const node_app_name = require("../sqltest");  // my db instance here
const { Model } = Sequelize;
class Player extends Model {}
Player.init({
firstname: Sequelize.STRING,
lastname: Sequelize.STRING,
},
{sequelize: node_app_name.sequelizeDB});
class Team extends Model {}
Team.init({
name: Sequelize.STRING
},
{sequelize: node_app_name.sequelizeDB});
class TeamPlayer extends Model {}
TeamPlayer.init({
id_team: Sequelize.INTEGER,
id_player: Sequelize.INTEGER,
},
{sequelize: node_app_name.sequelizeDB}); 
Player.belongsToMany(Team, {foreignKey: 'id_player', through: TeamPlayer});
Team.belongsToMany (Player,{foreignKey: 'id_team', through: TeamPlayer});
Player.findAll({    
include: 
[{
model: Team,
required: false
}]
}).then(pat => {
console.log(pat);
});

希望这有帮助。 请注意，belongsToMany()关联在初始化两个模型后发生。