To-One Relation Filters
Filter by singular relation slots using is and isNot
is
Match records where the related record matches conditions:
// Posts by admin author
const adminPosts = await client.post.findMany({
where: {
author: {
is: { role: "ADMIN" },
},
},
});
// Users with verified profile
const verifiedUsers = await client.user.findMany({
where: {
profile: {
is: { verified: true },
},
},
});
isNot
Exclude records where the related record matches conditions:
// Posts not by banned authors
const posts = await client.post.findMany({
where: {
author: {
isNot: { status: "BANNED" },
},
},
});
isNot: P is the negation of is: P: it matches when the related record does
not match P, and on an optional relation also when there is no related
record. A related record whose compared field is NULL does not match P, so
isNot keeps it: above, posts whose author’s status is NULL are returned.
This differs from NOT on a model’s own field, which follows SQL’s
three-valued logic and drops rows whose field is NULL, and from Prisma, whose
isNot drops them too. To drop them, put the negation inside is:
// Posts whose author has a status other than BANNED
const posts = await client.post.findMany({
where: {
author: {
is: { NOT: { status: "BANNED" } },
},
},
});
On an optional relation this form also drops records with no related record.
Null Checks
Null checks are available only when the to-one relation is optional. Bare
null is shorthand for { is: null }.
// Users with a profile
const withProfile = await client.user.findMany({
where: {
profile: {
isNot: null,
},
},
});
// Users without a profile
const withoutProfile = await client.user.findMany({
where: {
profile: { is: null },
},
});
// Equivalent shorthand
const alsoWithoutProfile = await client.user.findMany({
where: { profile: null },
});
An empty optional relation is not an orphaned membership. An orphaned
membership has non-empty stored identity whose target row is missing; that is
invalid data and relation projection fails rather than returning null.
Direct polymorphic relations are variant-aware: target filters include a
type, while optional-presence checks accept bare null, { is: null }, and
{ isNot: null }. Singular polymorphic inverse slots use the ordinary forms
shown above. See Polymorphic Relations.
Recursive filters
On a self relation, recurse turns the filter into a quantifier over every
row the relation reaches transitively: parent with recurse ranges over the
ancestors. Because that closure is a set, a recursive to-one filter takes
some, every and none instead of is/isNot:
// The document itself or any ancestor carries a grant to the user
const readable = await client.document.findMany({
where: {
parent: { recurse: true, self: true, some: { grants: { some: { userId } } } },
},
});
// No ancestor is archived (true for a root)
const live = await client.document.findMany({
where: { parent: { recurse: { depth: false }, none: { archived: true } } },
});
self: true adds the document itself to the closure, and recurse takes the
same options as in select. See Recursive filters
for the depth, cycle and provider rules. { parent: { some: … } } without
recurse is still refused: one hop of a to-one relation is is/isNot.
An object without is or isNot is read as is ({ author: { role: "ADMIN" } }
is { author: { is: { role: "ADMIN" } } }). With the key recurse, it is
always the recursive filter. To filter a target field that is literally named
recurse, spell is: { parent: { is: { recurse: … } } }.
Nested Conditions
Filter deeply nested relations:
// Posts by authors from specific organization
const posts = await client.post.findMany({
where: {
author: {
is: {
organization: {
is: { name: "Acme Corp" },
},
},
},
},
});
// Comments on posts by verified authors
const comments = await client.comment.findMany({
where: {
post: {
is: {
author: {
is: { verified: true },
},
},
},
},
});
Combined Filters
// Posts by active admin authors
const posts = await client.post.findMany({
where: {
author: {
is: {
role: "ADMIN",
active: true,
},
},
published: true,
},
});
// Users with premium subscription
const premiumUsers = await client.user.findMany({
where: {
subscription: {
is: {
plan: "PREMIUM",
active: true,
expiresAt: { gt: new Date() },
},
},
},
});
Examples
Find Posts by Author Email
async function getPostsByAuthorEmail(email: string) {
return client.post.findMany({
where: {
author: {
is: { email },
},
},
include: { author: true },
});
}