Prisma 3 relation aggregation (sum)

Question

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:

1 List by ID
Included list items
And for every item sum of the values (from Vote table).

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!

user_145435 · Answer

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.

Prisma 3 relation aggregation (sum)

1 Answers