Filter records based on fields of related models — traverse forward relations, reverse relations, and multi-level relation chains.

Cerial supports filtering records based on the fields of related models. You can traverse forward relations, reverse relations, and even multi-level relation chains to build expressive queries.

Basic Nested Filtering

Filter on a related model's fields by nesting conditions under the relation field name:

// Find users whose profile bio contains 'developer'
const users = await client.db.User.findMany({
  where: {
    profile: { bio: { contains: 'developer' } },
  },
});

The filter is applied as a subquery — it finds records where at least one related record matches the criteria.

Forward Relations

Forward relations are defined with the @field() decorator and represent a direct reference from one model to another:

// Schema:
// model Post {
//   authorId Record
//   author Relation @field(authorId) @model(User)
// }

// Find posts by a specific author name
const posts = await client.db.Post.findMany({
  where: {
    author: { name: 'Alice' },
  },
});

Reverse Relations

Reverse relations are the inverse of forward relations — they represent the "has many" or "has one" side:

// Schema:
// model User {
//   posts Relation[] @model(Post)
// }

// Find users who have at least one published post
const users = await client.db.User.findMany({
  where: {
    posts: { status: 'published' },
  },
});

You can apply multiple conditions to the related model's fields. All conditions within a nested relation filter are ANDed together — the related record must match all specified conditions:

// Find users who have posts about TypeScript created in 2024
const users = await client.db.User.findMany({
  where: {
    posts: {
      title: { contains: 'TypeScript' },
      createdAt: { gte: new Date('2024-01-01') },
    },
  },
});

Multi-Level Nesting

You can chain nested filters through multiple levels of relations:

// Find posts where the author works at a tech company
const posts = await client.db.Post.findMany({
  where: {
    author: {
      company: {
        name: { startsWith: 'Tech' },
      },
    },
  },
});

// Find categories that contain posts by active users
const categories = await client.db.Category.findMany({
  where: {
    posts: {
      author: {
        isActive: true,
        role: { in: ['admin', 'editor'] },
      },
    },
  },
});

Using All Filter Operators

All standard filter operators work inside nested relation filters — comparison, string, array, logical, and special operators:

const users = await client.db.User.findMany({
  where: {
    posts: {
      // Comparison
      viewCount: { gte: 100 },
      // String
      title: { startsWith: 'Guide' },
      // Array
      tags: { hasAny: ['typescript', 'javascript'] },
      // Special
      deletedAt: { isNull: true },
    },
  },
});

Logical Operators in Nested Filters

const users = await client.db.User.findMany({
  where: {
    posts: {
      OR: [{ status: 'published' }, { status: 'featured' }],
    },
  },
});

const users = await client.db.User.findMany({
  where: {
    posts: {
      NOT: { status: 'draft' },
      createdAt: { gte: new Date('2024-01-01') },
    },
  },
});

Combining Nested and Top-Level Filters

Nested relation filters can be combined with direct field filters on the parent model:

const users = await client.db.User.findMany({
  where: {
    // Direct field filter
    isActive: true,
    age: { gte: 18 },
    // Nested relation filter
    posts: { status: 'published' },
    profile: { bio: { isNone: false } },
  },
});

Works Across All Query Methods

Nested relation filters work in where clauses for all query methods that support filtering:

// Count users with published posts
const count = await client.db.User.count({
  posts: { status: 'published' },
});

// Check if any user has a verified profile
const hasVerified = await client.db.User.exists({
  profile: { isVerified: true },
});

// Update users who have overdue tasks
await client.db.User.updateMany({
  where: {
    tasks: { dueDate: { lt: new Date() } },
  },
  data: { hasOverdueTasks: true },
});

// Delete posts by inactive authors
await client.db.Post.deleteMany({
  where: {
    author: { isActive: false },
  },
});

Nested Relation Filtering