Relation Filters
Filter records by their related models
Overview
Filter records by their relations using operators that depend on cardinality:
is/isNot for to-one relations, and some/every/none for to-many. On a
self relation, recurse follows the relation transitively (see
Recursive filters).
Direct polymorphic filters first select a target type; polymorphic inverses
use their declared to-one or to-many cardinality.
To-One Relations
For singular slots (s.toOne):
| Operator | Description |
|---|---|
is |
Match related record |
isNot |
Exclude matching related record |
To-Many Relations
For collection slots (s.toMany):
| Operator | Description |
|---|---|
some |
At least one match |
every |
All must match |
none |
No matches |
Recursive filters
On a self relation — a foreign key such as parent/children, or a
junction such as team.parents/team.children — a filter can follow the
relation transitively with recurse. It then quantifies over the relation’s
closure: every row reached by one or more hops, plus the row itself with
self: true.
// Documents that carry a grant to the user, or whose ancestor does
await client.document.findMany({
where: {
parent: { recurse: true, self: true, some: { grants: { some: { userId } } } },
},
});
// Documents whose root is personal: the root is the closure row with no parent
await client.document.findMany({
where: {
parent: { recurse: true, self: true, some: { parentId: null, personal: true } },
},
});
// Pages granted to a team the user belongs to or leads, or to any team above it
await client.page.updateMany({
where: {
grants: {
some: {
level: { in: ["edit", "full"] },
granteeTeam: {
children: {
recurse: true,
self: true,
some: {
OR: [
{ members: { some: { userId } } },
{ grants: { some: { userId, level: "full" } } },
],
},
},
},
},
},
},
data: { title },
});
| Key | Meaning |
|---|---|
recurse: true or recurse: {} |
follow the relation up to 100 hops |
recurse: { depth: n } |
follow it up to n hops, 1 ≤ n ≤ 1000 |
recurse: { depth: false } |
exhaustive: follow it until the data ends |
recurse: { preventCycles } |
junction relations only; accepted for parity with select, the closure is the same set either way |
recurse: { selective: true } |
you state that the rows some/none names, and the rows that reach them, are few (see Selective walks below) |
self: true |
include the row itself (hop 0); default false |
some: where |
at least one row of the closure matches |
every: where |
every row of the closure matches; true when the closure is empty |
none: where |
no row of the closure matches; true when the closure is empty |
recurse takes the same options as in select/include,
except maxOccurrences, which a filter has nothing to bound, and
fewRows, which reads each output row’s walk by key (a filter outputs no
walk), plus selective, which only a filter states. It is admitted on the same relations: an ordinary self relation, foreign key
or junction, whose model has a complete primary key. Anywhere else it is
refused with a ValidationError. At least one of some, every and none
is required, several are combined with AND, and each takes the related
model’s full where, including other relation filters, other recursive
filters and AND/OR/NOT. With recurse, a to-one relation such as
parent takes some/every/none too, because the closure is a set even
when one hop reaches one row; is and isNot cannot be combined with it.
A fragment returned by a helper keeps recurse: true literal only with
as const (or satisfies): TypeScript otherwise widens it to boolean,
which recurse does not accept.
The closure is a set keyed by the primary key. A cycle terminates, and a row
reached by two paths (a diamond) counts once. A walk that comes back to its
start makes the start a member, even without self. Unlike a recursive
select, a filter never refuses a foreign-key cycle: there is no tree to
build. A numeric depth counts hops from the starting row.
A recursive filter works wherever a relation filter does: every read, count,
exist, aggregate, groupBy, a relation projection’s where, a _count
filter, updateMany and deleteMany, and nested inside other relation
filters. It compiles to one correlated WITH RECURSIVE inside EXISTS, in the
same statement as the rest of the query.
- Row scopes. With soft delete or another
extension whose
rulehides rows, the walk reads the related domain at every hop: a hidden row is not a member and stops the walk below it. Theselfrow is the row the call already selected and is not filtered again. - MySQL. An
updateManyordeleteManyfiltered through a relation reads the matching keys before it changes any row, so it matches the rows PostgreSQL and SQLite match, even when the walk crosses the model being changed or a model its delete cascades into. An exhaustive walk deeper thancte_max_recursion_depthfails with the driver’s error 3636. - Cost. The walk runs once per outer row. Index the foreign key
(
parentId) and the junction column the walk starts from (parentIdofteamParentto walkchildren,childIdto walkparents). A numericdepth(andrecurse: true, which is 100) carries the hop count with each row, so a cycle in the data is walked again at every level up to the depth, and the nestedsome/every/nonecondition is checked once per row and level reached;{ depth: false }visits each row once. On PostgreSQL,ANALYZEa table after loading it in bulk: without statistics the planner overestimates the walk, and JIT compilation can then add seconds to every statement. - Selective walks.
recurse: { depth: false, selective: true }states a fact about your data that the schema cannot: few rows match the walk’ssome(ornone), and few rows reach them, such as “the teams the actor belongs to, through their subteams”. Where a statement shares relation bodies (PostgreSQL drivers, D1) and reads the walk only inside bodies it already computes over their whole tables, as a permissions policy’s repeated rules do, the walk is then computed once, from those few rows back, instead of once per row of each body. Everywhere else it stays in place, so a unique read or a keyed write keeps its index. It is never applied toevery, which reaches back from the rows its predicate does not hold of, nor to a walk merged with one that does not state it. Index the columns itssomereads (userId): computed once, the walk first finds every row they match. State it only when it is true: on a walk whose rows are many, computing it once reads most of its table (on SQLite, a read over 30,000 teams went from 45 to 61 ms). Without it, nothing is guessed: the walk is computed for each row that reads it. The SQL and the plans with and without it are in Performance opt-ins.
Polymorphic Relations
A direct polymorphic relation combines one public target discriminator with a
target-specific is or isNot filter. Optional direct storage also supports
bare null, { is: null }, and { isNot: null }. An inverse polymorphic
relation uses the ordinary operator family for its declared cardinality. See
Polymorphic Relations.
Quick Examples
// Users with admin profile
await client.user.findMany({
where: {
profile: {
is: { role: "ADMIN" },
},
},
});
// Users with at least one published post
await client.user.findMany({
where: {
posts: {
some: { published: true },
},
},
});
// Posts with all approved comments
await client.post.findMany({
where: {
comments: {
every: { approved: true },
},
},
});