How to get the count of a relationship using Prisma

April 19th, 2023

Example Problem

Imagine we want to build a page to display all our posts including the number of comments on each post. Something like this…

Prisma Schema

Let’s start off by defining a Prisma schema that we can use in our example. We’ll create a Post and Comment table. Each post can have many comments on it.

model Post {
  id        Int       @id @default(autoincrement())
  title     String
  body      String
  comments  Comment[]
  createdAt DateTime  @default(now())
}

model Comment {
  id        Int      @id @default(autoincrement())
  comment   String
  author    String
  post      Post     @relation(fields: [postId], references: [id])
  postId    Int
  createdAt DateTime @default(now())
}

Code

You can use the include property and the _count property inside of include to the get the count of any relationships.

This will add a _count property on your query results where you can get the count of the comments.

const prisma = new PrismaClient();

const postsWithCommentCount = await prisma.post.findMany({
  include: {
    _count: {
      select: {
        comments: true,
      }
    }
  }
})

return {
  posts: postsWithCommentCount.map((post) => ({
    id: post.id,
    title: post.title,
    body: post.body,
    commentCount: post._count.comments
    createdAt: post.createdAt,
  }))
}