I'm having some trouble trying to get sum for related data.
This is my schema:
model Vote {
userId Int
user User @relation(fields: [userId], references: [id])
itemId Int
item Item @relation(fields: [itemId], references: [id])
value Int
@@id([userId, itemId])
}
model Item {
id Int @id @default(autoincrement())
userId Int
user User @relation(fields: [userId], references: [id])
listId Int
list List @relation(fields: [listId], references: [id])
votes Vote[]
body String @db.VarChar(255)
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model List {
id Int @id @default(autoincrement())
userId Int
user User @relation(name: "Ownership", fields: [userId], references: [id])
items Item[]
sharedWith User[] @relation(name: "Shared")
title String @db.VarChar(255)
description String?
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
model User {
id Int @id @default(autoincrement())
email String @unique
password String
name String?
lists List[] @relation(name: "Ownership")
items Item[]
votes Vote[]
sharedWith List[] @relation(name: "Shared")
createdAt DateTime @default(now())
updatedAt DateTime @updatedAt
}
What I'm trying to get is:
This is what I came up with:
prisma.list.findUnique({
where: { id },
include: {
items: {
include: {
votes: {
include: {
_sum: {
select: {
value: true,
},
},
},
},
},
},
},
})
I constructed query based on multiple different queries I found online (most of them in Prisam Docs).
Also I noticed that Prisma Docs are missing a lot of things, like (adding/removing related data - many to many relationship). Are there any better docs, or example project with stuff like these?
Thanks!
This isn't currently possible as a single query. What you can do is sum over the values after you get them back.
I created a similar example based on StackOverflow. Given the following Prisma Schema:
// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
}
generator client {
provider = "prisma-client-js"
}
model User {
id Int @id @default(autoincrement())
name String
votes Vote[]
questions Question[]
}
model Question {
id Int @id @default(autoincrement())
question String
votes Vote[]
user User @relation(fields: [userId], references: [id])
userId Int
}
model Vote {
id Int @id @default(autoincrement())
value Int @default(1)
questionId Int
question Question @relation(fields: [questionId], references: [id])
user User @relation(fields: [userId], references: [id])
userId Int
}
You can write the following script:
import { PrismaClient } from "@prisma/client"
const prisma = new PrismaClient()
async function main() {
const users = await prisma.user.findMany({
include: {
questions: {
include: {
votes: {
select: {
value: true,
},
},
},
},
},
})
for (let user of users) {
for (let question of user.questions) {
let vote_sum = 0
for (let vote of question.votes) {
vote_sum += vote.value
}
;(question as any).vote_sum = vote_sum
}
}
console.dir(users, { depth: Infinity })
}
main().catch((err) => console.error(err))
Then sum over the values after getting the data back.
If this doesn't work well for you, I would encourage you to write a feature request for the Prisma team.