Prisma Count by nested fields (with many-to-many)

user_18875136 0
Viewed 798

In my prisma schema I have something like that:

model Parent {
    id       String  @id @db.Uuid
    children Child[]
}

model ChildOfChild {
    id       String @id @db.Uuid
    parentId String @map("parent_id") @db.Uuid
    status   Status
    parent   Child  @relation(fields: [parentId], references: [id])

}

model Child {
    id       String         @id @db.Uuid
    parentId String         @map("parent_id") @db.Uuid
    parent   Parent         @relation(fields: [parentId], references: [id])
    children ChildOfChild[]
}

And I want to do something like: Count by: Parent.id and Parent.children.children.status.

With querybuilder it should look something like:

qb //root is Parent with alias r
    .leftJoin("r.children", "s")
    .leftJoin("s.children", "p")
    .groupBy(["id", "p.status"])
    .select(["count(*) as count", "r.id", "p.status"])

Can I do something like that in Prisma or should I resort to raw SQL query?

prisma typescript aggregation
1 Answers

You can use the count operator to get count for nested fields: Count Reference

Here's an example of using count operator:

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function main() {
  await prisma.parent.count({
    where: {
      id: '1',
      children: {
        every: {
          children: {
            every: {
              status: 'active',
            },
          },
        },
      },
    },
  });
}

main()
  .catch((e) => {
    throw e;
  })
  .finally(async () => {
    await prisma.$disconnect();
  });
user_41540620
Related