Skip to content
VibORM
Esc
↑↓navigate↵open⌘Jpreview
On this page

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 rule hides rows, the walk reads the related domain at every hop: a hidden row is not a member and stops the walk below it. The self row is the row the call already selected and is not filtered again.
  • MySQL. An updateMany or deleteMany filtered 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 than cte_max_recursion_depth fails 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 (parentId of teamParent to walk children, childId to walk parents). A numeric depth (and recurse: 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 nested some/every/none condition is checked once per row and level reached; { depth: false } visits each row once. On PostgreSQL, ANALYZE a 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’s some (or none), 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 to every, 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 its some reads (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 },
    },
  },
});

Pages

Was this page helpful?