How to select related table's related table using prisma?

Question

I have prisma schema like this:

model Permission {
  id    String            @id @default(cuid())
  name  String
  roles RolePermissions[]
}

model Role {
  id          String            @id @default(cuid())
  name        String
  permissions RolePermissions[]
  users       UserRoles[]
}

model User {
  id            String      @id @default(cuid())
  name          String?
  email         String?     @unique
  emailVerified DateTime?
  image         String?
  roles         UserRoles[]
}

model UserRoles {
  user       User     @relation(fields: [userId], references: [id])
  userId     String
  role       Role     @relation(fields: [roleId], references: [id])
  roleId     String
  assignedAt DateTime @default(now())
  assignedBy String?

  @@id([userId, roleId])
}

model RolePermissions {
  role         Role       @relation(fields: [roleId], references: [id])
  roleId       String
  permission   Permission @relation(fields: [permissionId], references: [id])
  permissionId String
  assignedAt   DateTime   @default(now())
  assignedBy   String?

  @@id([roleId, permissionId])
}

So, each user can have multiple roles and each role can have multiple permissions, which, as you understand, means each user has multiple permissions.

My goal is to select user's permisions, based on which roles he assigned to.

Proplem is, what I don't understand how to do it:

I know, I can select user's roles by

 const res  = await prisma.user.findFirst(
        {
            include: {
                roles: true
            }
        }
    )

So, it will give me user's roles, but I need to get permissions each of user's roles.

I thought, I can do it like this:

 const res  = await prisma.user.findFirst(
        {
            include: {
                roles: {
                    include: {
                        permissions: true
                    }
                }
            }
        }
    )

But this gives me error:

So, how can I get user's permissions from user's roles?

Answer 1

Like the error says (albeit not very clearly) permissions does not exist in the UserRoles table. It's in the Role table. You need to go 3 levels deep.

const res  = await prisma.user.findFirst(
  {
    include: {
      roles: {
        include: {
          role: {
            include: {
              permissions: true
            }
          }
        }
      }
    }
  }
)

Not sure if my syntax is correct, but you get the point.