Is it possible to filter nested queries based on partial top-level results?

[TomerAberbach]

For example, suppose you have a one-to-many relationship between schools and classes (a school has many classes; a class belongs to one school). I have the ID of some class and what I want is to query that class, its school, and all classes that are in that school and that are from the same year as the class I have the ID for:

prisma.db.class.findUnique({
  select: {
    year: true,
    name: true,
    school: {
      select: {
        name: true,
        classes: {
          // How do I only get classes in the same year as the top-level class? 
        }
      }
    }
  },
  where: { id: `some-class-id` } 
})

I think the problem here is that I don't know what the top-level class's year is in advance so I can't filter the results by the year. This is how I would do it in SQL:

select
  MainClass.year,
  MainClass.name mainClassName,
  School.name as schoolName,
  SchoolClass.id as schoolClassId
  -- etc
from Class MainClass
left join School on MainClass.schoolId = School.id
left join Class SchoolClass on SchoolClass.schoolId = School.id and SchoolClass.year = MainClass.year
where MainClass.id = "some-class-id"

Am I missing something? Is this possible without raw SQL?

If this is not possible, then it seems I have three options right?

1. Use raw SQL
2. Two Prisma queries: 1 for the class, then 1 for the school that uses the class's year (from the result of the first query) in the where clause
3. One Prisma query: query the class and all of the classes of the school, then filter the result on the server to only classes in the same year as the top-level class

Also, if there isn't already a feature request for this, then I would be happy to file one. Thanks in advance!

[austincrim]

Hey @TomerAberbach 👋 ,

This is not possible yet with the Prisma Client, though we have an outstanding feature request #5048. The 3 possible options you outlined are the same I thought of, so I would move forward with one of those for now.

You could also add your specific use case to #5048 and it will help our engineers as they look to design potential solutions!

[TomerAberbach]

Thanks! I'll add my use case to that issue

[Naryalin]

Hey, did you find a solution to this? I'm trying to use the results of the nested query to filter the solutions as this:

super(prisma.team_Member, {
      include: {
        Team: {
          include: {
            Challenges: {
              include: {
                Challenge:{
                  select:{
                    Stages:{
                      select:{
                        Solutions: {
                          where:{
                            ID_Team: { equals: prisma.team.fields.ID}
                          }
                        },
                      }
                    }
                  }
                },
              }
            }
          },
        },
      },
    });

But it is retrieving all the solutions per stage rather than filtering by the Team

Models:

// SOLUTION
model Solution {
  Title String
  Description       String @db.Text
  Documentation_Url String @db.Text
  Video_Url String @db.Text
  Presentation_Url String @db.Text

  // Many to One relation with Stage
  ID_Stage Int
  Stage    Stage @relation(fields: [ID_Stage], references: [ID])

  // Many to One relation with Team
  ID_Team Int
  Team    Team @relation(fields: [ID_Team], references: [ID], onDelete: Cascade)

  @@id([ID_Stage, ID_Team])
}
model Stage {
  ID   Int      @id @default(autoincrement())
  Name String
  From DateTime
  To   DateTime

  // One to Many relation with Solution
  Solutions Solution[]

  // Many to One relation with Challenge
  ID_Challenge Int
  Challenge    Challenge @relation(fields: [ID_Challenge], references: [ID], onDelete: Cascade)
}
// TEAM
model Team {
  ID   Int    @id @default(autoincrement())
  Name String

  // Many to Many relation with User
  Users Team_Member[]

  // One to Many relation with Solution
  Solutions Solution[]

  // Many to Many relation with Challenge
  Challenges Challenge_Team[]
}

model Team_Member {
  ID_Team  Int
  Team     Team    @relation(fields: [ID_Team], references: [ID], onDelete: Cascade, onUpdate: Cascade)
  ID_User  Int
  User     User    @relation(fields: [ID_User], references: [ID], onDelete: Cascade, onUpdate: Cascade)
  isLeader Boolean @default(false)

  @@id([ID_Team, ID_User])
}

[ludralph]

Hi there,

To keep our discussions organized and focused on the most relevant topics, we’re reviewing and tidying up our backlog. As part of this process, we’re closing discussions that have already been marked as answered but remain open.

If this discussion still requires further input or clarification, feel free to reopen it or start a new one with updated details. Your contributions are invaluable to the community, and we’re here to help!

For more details about our priorities and vision for the future of Prisma ORM, check out our latest blog post: https://www.prisma.io/blog/prisma-orm-manifesto.

Thank you for your understanding and ongoing support of the Prisma community!