Filtering on related tables using Prisma and Postgres

Question

I have a Prisma model for a Postgres database with a User that can belong to many Organisations, and organizations that can have many users. So I've created a typical many-to-many relationship.

model Organization {
  organizationId Int      @id @default(autoincrement())
  slug           String   @unique
  name           String
  users          UserInOrganization[]
}

model User {
  userId        Int      @id @default(autoincrement())
  name          String
  organizations UserInOrganization[]
}

model UserInOrganization {
  fkOrganizationId Int
  fkUserId         Int
  organization     Organization @relation(fields: [fkOrganizationId], references: [organizationId], onDelete: Cascade)
  user             User         @relation(fields: [fkUserId], references: [userId], onDelete: Cascade)
  role             Role         @relation(fields: [fkRoleId], references: [roleId])

  @@id([fkOrganizationId, fkUserId])
}

Next, I need to get all the organizations for a specific user and slug.

If I'd write it in SQL I'd do something like:

select o.name, u."userId" from "Organization" as o 
join "UserInOrganization" as uio on o."organizationId" = uio."fkOrganizationId" 
join "User" as u on u."userId" = uio."fkUserId" 
where u."userId" = 1 and o.slug='slug'

But what would that be in Prisma? Doing something like below seems to give me all the organizations matching the slug (not considering the where on the userId). The where-clause is just filtering the users for each organization. 🥺

const organization = await prisma.organization.findUnique({
  where: { slug: slug },
  select: {
    name: true,
    users: { where: { user: { userId: userId } } },
  },
});

How can I get just the organizations that have the actual userId?

Answer 1

You can use include with nested select to get organisations that match slug and userId.

Here's the query for it:

  const organization = await prisma.organization.findUnique({
    where: {
      slug: 'prisma',
    },
    include: {
      users: {
        where: {
          user: {
            userId: 1,
          },
        },
        select: {
          user: true,
        },
      },
    },
  });

  console.log(JSON.stringify(organization, null, 2));

Here's the output:

{
  "organizationId": 1,
  "slug": "prisma",
  "name": "Prisma",
  "users": [
    {
      "user": {
        "userId": 1,
        "name": "John Doe"
      }
    }
  ]
}