In my database, I have implemented a structure with 3 tables: Member, Characters, and MemberCharacters. Each member can have multiple Characters, and each Character can be used by multiple Members. To handle this many-to-many relationship, I have utilized the MemberCharacters table, which stores the memberId and characterIds as foreign keys. Here is an overview of the table structure:
model Members {
id String @unique @id @default(cuid())
displayName String
characters MemberCharacters[]
}
model Characters {
id Int @id @default(autoincrement())
name String
pictureUrl String
members MemberCharacters[]
}
model MemberCharacters {
id String @id @default(cuid())
memberId String
Members Members @relation(fields: [memberId], references: [id])
characterId Int
Characters Characters @relation(fields: [characterId], references: [id])
}
Now, I am looking for a way to retrieve a JSON response containing all details about the Member along with a list of the Characters assigned to that Member.
In the Prisma documentation, I came across the option of using 'include'. Although this approach almost fulfills my requirements, it returns a JSON with all Member information and a list of MemberCharacter JSON objects. However, I specifically need the actual Character objects associated with those MemberCharacters in the list.
let result = await prisma.members.findMany({
include: {
characters: true
}
})
console.log(result)
Current Result:
{
id: '86548974659889456',
displayName: 'DisplayName',
characters: [ [Object], [Object], [Object], [Object], [Object] ]
}
Although the Prisma documentation suggests a different approach using just two tables, it is not suitable for my scenario as I cannot modify the data in Characters table. Therefore, I must stick with the original setup.
model Post {
id Int @id @default(autoincrement())
title String
categories Category[]
}
model Category {
id Int @id @default(autoincrement())
name String
posts Post[]
}