Data modeling in Prisma with relations

Question

I have a problem understanding relations and data-modeling in Prisma. I have an "easy" example about two users who can participate in a tennis game. So i have:

Model User {
  id      Int @id 
  name    String 
}

Model Game { 
  id        Int @id 
  player1   PlayerInGame
  player2   PlayerInGame
}

Model PlayerInGame {
  id      Int @id
  player  User
  game    Game
}

It gives me this error:

Error validating model "Game": Ambiguous relation detected. The fields `player1` and `player2` in model `Game` both refer to `PlayerInGame`. Please provide different relation names for them by adding `@relation(<name>).

How can i fix this? Thanks in advance.

I tried to at a @relation field as well, but that gave me the following error:

model Game {
  id        Int          @id @default(autoincrement())
  createdAt DateTime     @default(now())
  player1   PlayerInGame @relation("player1")
  player2   PlayerInGame  @relation("player2")
}


Error validating model "Game": Automatic related field generation would cause a naming conflict. Please add an explicit opposite relation field.

score 20 · Answer 1 · answered Aug 10 '20 at 07:25

You can fix this error by providing two relation fields on PlayerInGame. You have two relation fields on Game. Hence you also need two opposite relation fields on PlayerInGame. In your application logic you have to ensure that only one of those two relation fields is set. So only PlayerInGame.gameAsPlayer1 or PlayerInGame.gameAsPlayer2 should be set according to your business domain.

model User {
  id      Int @id 
  name    String 
}

model Game { 
  id        Int @id 
  player1   PlayerInGame @relation("GamePlayer1")
  player2   PlayerInGame @relation("GamePlayer2")
}

model PlayerInGame {
  id            Int   @id
  player        User
  gameAsPlayer1 Game? @relation("GamePlayer1")
  gameAsPlayer2 Game? @relation("GamePlayer2")
}

score 4 · Answer 2 · answered Aug 10 '20 at 06:44

4

The best way to do it would be a many-to-many relation. You can add as many users to a game as well in the future and will be scalable in turn.

model User {
  id    Int    @id
  name  String
  games Game[]
}

model Game {
  id    Int    @id
  name  String
  users User[]
}

You can then conditionally check the number of players in the game and limit them in your business logic.

answered Aug 10 '20 at 06:44

Ryan

5,229
1
20
31

Thanks for your answer. But will this also work when i want to add some statistics, like games won, aces etc. ? That will be different per game and user, so that is why I thought about adding PlayerInGame. – Royal Aug 10 '20 at 07:04

score 1 · Answer 3 · answered Jun 15 '22 at 11:12

In my case, I was having this issue due to a FK being used in multiple fields in the same column. Whenever you have more than 1 relation to a model you need to provide a relation name to disambiguate the relation.

example:

model User {
  id    Int    @id
  name  String
  games Game[]
}

model Game {
  game_id    Int    @id
  name       String
  users      User[]
}

model PlayerInGame {
  id              Int   @id
  player          User
  gameAsPlayer1   Game? @relation("GamePlayer1", fields: [gameAsPlayer1Id], references: [game_id])
  gameAsPlayer1Id Int
  gameAsPlayer2   Game? @relation("GamePlayer2", fields: [gameAsPlayer2Id], references: [game_id])
  gameAsPlayer2Id Int
}

Data modeling in Prisma with relations

3 Answers3