Querying models based on their relations existence

Problem

Common use cases when querying for relations from a database are:

  • finding all the entities for table X where the relation Y doesn't exist
  • finding all the entities for table X where the relation Y has at least one item

Taking a concrete example of users and posts as entities, this would translate to "find the users that haven't created any posts" and "find the users that have created at least one post".

Read below to learn how this can be achieved with Prisma.

Solution

Consider this Prisma schema:

model User {
id Int @id @default(autoincrement())
name String
posts Post[]
}
model Post {
id Int @id @default(autoincrement())
user User? @relation(fields: [userId], references: [id])
userId Int?
}

You can now query for the users that do not have any posts as follows:

await prisma.user.findMany({
where: { posts: { none: {} } },
})

This query returns all the users that haven't created any posts yet.

In a similar way, you can query for users that created at least one post as follows:

await prisma.user.findMany({
where: { posts: { some: {} } },
})

This query returns all the users that created at least one post.

This article showed how you can query for entities based on if the relation exists.

Edit this page on GitHub