Search code examples
node.jstypescriptnestjstypeorm

TypeORM. Two Foreign Keys referencing the same Primary Key in one table

In my project I would like to have bi-directional ManyToOne - OneToMany relations with two foreign keys referencing to the same primary key. In my case it would be a table 'match' which contains two players from table 'player' (player1Id and player2Id are FK). I want to be able to get all matches where a particular player played as well as assign a player to the match. In Match entity I guess it should be something like this: 

@Entity()
export class Match {

@PrimaryGeneratedColumn()
id!: number;

@ManyToOne((type) => Player)
@JoinColumn({ name: "player1Id", referencedColumnName: "id" })
player1: Player;

@ManyToOne((type) => Player)
@JoinColumn({ name: "player2Id", referencedColumnName: "id" })
player2: Player;
//some other properties...

but since I have to indicate one inverse-side entity in @OneToMany() decorator then how should it look like in Player entity? Is there any way to map such an association in TypeORM and is it a good and common practice to have two FK in one table referencing to the same primary key in another table? I'm new in NodeJS and webdev in general. Thanks for any help.

Solution

  • This looks to me like you have a many to many relation here. Match has many players (even if you only need two, and this this you could always scale the number of players), and a player can be at many matches.

    @Entity()
export class Match {

@PrimaryGeneratedColumn()
id!: number;

@ManyToMany((type) => Player, (player) => player.matches)
players: Player[];

//some other properties...

    The player entity. If you do indeed make the relation many to many you must place @JoinTable() at one of the entities.

    @Entity()
export class Player{

@PrimaryGeneratedColumn()
id!: number;

@ManyToMany((type) => Match, (match) => match.players)
@JoinTable()
matches: Match[];

//some other properties...