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!

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!

• javascript
• postgresql
• prisma

1 Answer 1

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.