postgresql - sequelize-typescript 多对多关系模型数据

Question

我正在使用 sequelize 和 sequelize-typescript库，并试图实现以下关系:

团队.ts

@Scopes({
  withPlayers: {
    include: [{model: () => User}]
  }
})
@Table
export default class Team extends Model<Team> {

  @AllowNull(false)
  @Column
  name: string;

  @BelongsToMany(() => User, () => TeamPlayer)
  players: User[];
}

用户.ts

@Scopes({
  withTeams: {
    include: [{model: () => Team, include: [ () => User ]}]
  }
})
@Table
export default class User extends Model<User> {

  @AllowNull(false)
  @Column
  firstName: string;

  @AllowNull(false)
  @Column
  lastName: string;

  @BelongsToMany(() => Team, () => TeamPlayer)
  teams: Team[];
}

TeamPlayer.ts

@DefaultScope({
  include: [() => Team, () => User],
  attributes: ['number']
})
@Table
export default class TeamPlayer extends Model<TeamPlayer> {

  @ForeignKey(() => User)
  @Column
  userId: number;

  @ForeignKey(() => Team)
  @Column
  teamId: number;

  @Unique
  @Column
  number: number;
}

现在查询播放器时，您将获得包含以下数据的对象:

{
  "id": 1,
  "name": "Doe's Team",
  "players": [
    {
      "id": 1,
      "firstName": "John",
      "lastName": "Doe",
      "TeamPlayer": {
        "userId": 1,
        "teamId": 1,
        "number": 32
    }
 }]
}

现在有几件事我无法完成..

1) 我想将 TeamPlayer 重命名为类似“membership”的名称；但不是通过更改类的名称2) TeamPlayer 的内容不应该有 id，但我希望它包含团队的数据，例如:

{
  "firstName": "John",
  "lastName": "Doe"
  "membership": {
     "number": 32
 }

在上面的类中，我试图为 TeamPlayer 类设置一个范围，只在 TeamMember 包含中包含 number，但是没有效果。

我曾经让 TeamPlayer 类拥有 team 和 player 的直接成员资格，但该解决方案增加了冗余 id 到 TeamPlayer 类，也没有阻止团队中的重复成员。在这些情况下，我确实可以手动(= 在代码中)防止重复，但这并不优雅。

Answer 1

我最终通过添加从 TeamPlayer 到 User 和 Team 的一对多关系解决了这个问题，并且还想出了办法通过使用 @ForeignKey 添加两个字段来使 teamId + userId 对唯一，如下所示:

TeamPlayer.ts

@Table
export default class TeamPlayer extends Model<TeamPlayer> {

  @BelongsTo(() => Team)
  team: Team;

  @ForeignKey(() => Team)
  @PrimaryKey
  @Column
  teamId: number;

  @BelongsTo(() => User)
  user: User;

  @ForeignKey(() => User)
  @PrimaryKey
  @Column
  userId: number;

  @Column
  number: number;
}

User.ts

@Table
export default class User extends Model<User> {

  @AllowNull(false)
  @Column
  firstName: string;

  @AllowNull(false)
  @Column
  lastName: string;

  @HasMany(() => TeamPlayer)
  teams: TeamPlayer[];
}

Team.ts

export default class Team extends Model<Team> {
  @AllowNull(false)
  @Column
  name: string;

  @HasMany(() => TeamPlayer)
  players: TeamPlayer[];
}

此解决方案允许我通过范围控制包含的查询属性，并为我提供正确的对象输出。