I have an explicit many to many relation that looks like this
model Fighter {
id Int @id @default(autoincrement())
name String
image String?
description String?
battles Battle[]
votes Vote[]
}
model Vote {
id Int @id @default(autoincrement())
Fighter Fighter @relation(fields: [fighterId], references: [id])
fighterId Int
Battle Battle @relation(fields: [battleId], references: [id])
battleId Int
}
model Battle {
id Int @id @default(autoincrement())
slug String @unique
name String
fighters Fighter[]
votes Vote[]
}
A fighter has multiple battles, a battle has multiple fighters, and a vote belongs to a fighter and a battle. I want to retrieve a battle by slug with the fighters associated and count all votes that belongs to each fighter for this battle. Here is my query to perform that:
await prisma.battle.findUnique({
where: { slug },
include: {
fighters: {
include: {
_count: {
select: {
votes: true,
}
}
}
}
}
});
But with this query the vote count is not linked to the battle, it means that it will count all votes for the fighter and do not take into consideration the battle. I'm new to prisma so I believe there is a way to achieve what I want but I'm not able to do it.
afaik this isn't possible with the _count api. This is the best I can come up with:
Method 1:
just include votes and compute the count afterwards
const _battle = await prisma.battle.findUnique({
where: { slug },
include: {
fighters: {
include: {
votes: {
where: { Battle: { slug } },
},
},
},
},
});
const battle = {
..._battle,
fighters: _battle?.fighters.map(({ votes, ...rest }) => ({
...rest,
_count: { votes: votes.length },
})),
};
Method 2:
Get the vote counts using groupBy and map it back with the battle query
const [votes, _battle] = await Promise.all([
prisma.vote.groupBy({
where: { Battle: { slug } },
by: ['fighterId'],
_count: { _all: true },
}),
prisma.battle.findUnique({
where: { slug },
include: {
fighters: true,
},
}),
]);
const battle = {
..._battle,
fighters: _battle?.fighters.map((fighter) => ({
...fighter,
_count: {
votes: votes.find(({ fighterId }) => fighterId === fighter.id)?._count
._all,
},
})),
};
Both of these method will produce a response like this:
"battle": {
"id": 1,
"slug": "A",
"name": "A",
"fighters": [
{
"id": 1,
"name": "F1",
"image": null,
"description": null,
"_count": {
"votes": 2
}
},
{
"id": 2,
"name": "F2",
"image": null,
"description": null,
"_count": {
"votes": 1
}
}
]
}