Theo's Typesafe Cult•16mo ago

Prisma: best way to structure these D&D schemas?

Context: I'm using tRPC, Prisma, and MongoDB I have a few models like Campaign, Player, NPC, and Battle Campaigns have many Player/NPC/Battle:

model Campaign {
  id      String  @id @default(auto()) @map("_id") @db.ObjectId
  title   String
  battles Battle[]
  npcs    Npc[]
  players Player[]
}

model Npc {
  id          String    @id @default(auto()) @map("_id") @db.ObjectId
  campaignId  String    @db.ObjectId
  campaign    Campaign  @relation(fields: [campaignId], references: [id])
  name        String    @default("Please name me")
  forStory    Boolean   @default(false)
  known       Boolean   @default(false)
}

model Player {
  id             String   @id @default(auto()) @map("_id") @db.ObjectId
  campaignId     String   @db.ObjectId
  campaign       Campaign @relation(fields: [campaignId], references: [id])
  name           String   @default("Please name me")
  maxHp          Int      @default(5)
  passiveWisdom  Int      @default(10)
}

model Battle {
  id          String    @id @default(auto()) @map("_id") @db.ObjectId
  campaignId  String    @db.ObjectId
  campaign    Campaign  @relation(fields: [campaignId], references: [id])
  title       String    @default("Please name me")
}

model Campaign {
  id      String  @id @default(auto()) @map("_id") @db.ObjectId
  title   String
  battles Battle[]
  npcs    Npc[]
  players Player[]
}

model Npc {
  id          String    @id @default(auto()) @map("_id") @db.ObjectId
  campaignId  String    @db.ObjectId
  campaign    Campaign  @relation(fields: [campaignId], references: [id])
  name        String    @default("Please name me")
  forStory    Boolean   @default(false)
  known       Boolean   @default(false)
}

model Player {
  id             String   @id @default(auto()) @map("_id") @db.ObjectId
  campaignId     String   @db.ObjectId
  campaign       Campaign @relation(fields: [campaignId], references: [id])
  name           String   @default("Please name me")
  maxHp          Int      @default(5)
  passiveWisdom  Int      @default(10)
}

model Battle {
  id          String    @id @default(auto()) @map("_id") @db.ObjectId
  campaignId  String    @db.ObjectId
  campaign    Campaign  @relation(fields: [campaignId], references: [id])
  title       String    @default("Please name me")
}

Battles are meant to have many Player and many NPC But since there’s an order to battle I’m thinking I should create something like a BattleParticipant model which holds an order value and which entity it’s in regard to Each participant can have an entityType (a string value) so i know whether it’s a Player or NPC, and entityId so I can properly refer to it

model BattleParticipant {
  id          String    @id @default(auto()) @map("_id") @db.ObjectId
  title       String    @default("Please name me")
  order       Int       @default(0)
  entityType  String
  entityId    String    @db.ObjectId
}

model BattleParticipant {
  id          String    @id @default(auto()) @map("_id") @db.ObjectId
  title       String    @default("Please name me")
  order       Int       @default(0)
  entityType  String
  entityId    String    @db.ObjectId
}

I'll post my desired outcome and alternative I've thought of in a comment

1 Reply

sal.tor•16mo ago

To get all the data I want up front for the frontend, aka the battle order and each entity's information, I'd ideally be able to do a prisma query like this

prisma.battleparticipant.findMany({
  where: { id: battle_id },
  include: { entity: true }
})

prisma.battleparticipant.findMany({
  where: { id: battle_id },
  include: { entity: true }
})

But because entity isn’t an actual model but is instead one of multiple possible models, this wouldn't work (as far as I see right now) The alternative I can think of is to get all the BattleParticipants and then iterate over them and based on the entity type I can query the appropriate table and ultimately combine them

const participants = await prisma.battleparticipants.findMany({ where: { id: battle_id } })
const entityData = []
participants.forEach(participant => {
  if (participant.entityType === 'player') {
    entityData.push(prisma.player.findUnique({ where: { id: participant.entityId } })
  } else if (participant.entityType === 'npc') {
    entityData.push(prisma.npc.findUnique({ where: { id: participant.entityId } })
  }
})
await Promise.all(entityData)
return participants.map((p, index) => ({ ...p, entity: entityData[index] }))

const participants = await prisma.battleparticipants.findMany({ where: { id: battle_id } })
const entityData = []
participants.forEach(participant => {
  if (participant.entityType === 'player') {
    entityData.push(prisma.player.findUnique({ where: { id: participant.entityId } })
  } else if (participant.entityType === 'npc') {
    entityData.push(prisma.npc.findUnique({ where: { id: participant.entityId } })
  }
})
await Promise.all(entityData)
return participants.map((p, index) => ({ ...p, entity: entityData[index] }))

So, does this approach to make a BattleParticipant model make sense? Or is there a better way to get the data all at once? cc @Strike here's the additional context and schemas I mentioned I'd add to this thread Yeah, i dont know necessarily whether that’s a 1:1 to “conditional join” since i know a tiny bit of sql but that sounds right Hehe To add to that im expecting ill be making a monster model that is associated only with battles Oh shoot yeah that’s a good name for it, Union type Yeah that’s an interesting historical thread, I’m going to dig into it on desktop tomorrow. I think for now my idea might work with your earlier links

Gaming

Programming

Prisma: best way to structure these D&D schemas?