Hi, I have a schema that looks something like this,

model Course {
  id Int @id @default(autoincrement())
  // ...

  fee Fee[]
  @@map("courses")
}

model Fee {
  id        Int        @id @default(autoincrement())
  course_id Int
  course    Course     @relation(fields: [course_id], references: [id])
  feeItems  FeeItems[]
  // ...

  @@map("fees")
}

model FeeItems {
  id                    Int   @id @default(autoincrement())
  amount                 Float
  number_of_installment Int
  fee_id                Int
  fee                   Fee   @relation(fields: [fee_id], references: [id])

  // ...

  @@map("fee_items")
}

I'm trying filter course based on the sum of fee items What's the best way to do it?

Hey there! If I’m understanding correctly, you want a list of courses based on the sum of each course’s fees. I don’t believe there is a way within the ORM at the moment to run this exact kind of query. This is one of those scenarios where you may need to drop into the

$queryRaw

function and run a query similar to this to get these fine-grained results:

async function findCourseByAmount(amount: number) {
    await prisma.$queryRaw`
        SELECT "Course".*
        FROM "Course"
        LEFT JOIN "Fee" ON "Fee".course_id = "Course".id
        LEFT JOIN "FeeItems" ON "FeeItems".fee_id = "Fee".id
        GROUP BY "Course".id
        HAVING SUM("FeeItems".amount) > ${amount}
    `;
}

(This grabs all of the courses whose sum of fees is GREATER than a provided amount)