CODEWITHSUNDEEP
typescriptnext.jsprisma
Matthieu Raynaud de Fitte
Matthieu Raynaud de Fitte

Reputation: 2459

Prisma : how can I get all children on a condition?

I am attempting to use prisma on a many to many table ( referencing itself ) such a row will have children and grand children

I can get all rows without any issues but I am struggling to get how to order the data in a readable JSON that would prevent parsing on the front end

The expected output is as follows : the root of the returned JSON would only reference the parents, the parents would reference their children and the children would reference their own children

Important to note : I can only return elements where enabled = true

The table I use is called antecedant and follows this prisma schema :

model antecedant {
  id            Int           @id @default(autoincrement())
  name          String
  children      antecedant[]  @relation("antecedant_children")
  parent        antecedant?   @relation("antecedant_children", fields: [parent_id], references: [id])
  parent_id     Int?
  enabled       Boolean       @default(true)
  creation_date DateTime      @default(now())
  update_date   DateTime      @updatedAt
}

So far I have gotten this :

import { PrismaClient, Prisma } from '@prisma/client'

const prisma = new PrismaClient()

export default async function handler(req, res) {
    const ret = await prisma.antecedant.findMany({
        where: {
            enabled: true,
            parent_id: null
        },
        include: {
            children: true,
        }
    });
    res.status(200).json(ret)
}

This works very well for the parents but I cannot find how to impose conditions on the children ( such as enabled = true ) and it does not display the grand children at all

How can I return the data in a way that does not require any parsing ?

Upvotes: 2

Views: 5496

Answers (1)

Tasin Ishmam
Tasin Ishmam

Reputation: 7198

You can add where conditions inside include as well as doing arbitrary levels of nesting. This is what the syntax would look like for your use-case

let ret = await prisma.antecedant.findMany({
    where: {
        enabled: true,
        parent_id: null
    },
    include: {
        children: {
            where: {
                enabled: true,
            },
            include: {
                children: true  // grandchildren
                // you can pass an object and impose conditions like where: { enabled: true } here as well
            }
        },
    }
});

More information is available in the relation queries article in the Prisma docs.

Upvotes: 6

Related Questions