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

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 },
  });
}

Was this page helpful?