Filter

Filtering is an important topic because it's involved in many ORM operations, for example when you find records, selecting relations, and updating or deleting multiple records.

Throughout this section all samples are based on the following ZModel schema:

zenstack/schema.zmodel

Loading...

Basic filters

You can filter on scalar fields with values or operators as supported by the field type. The following filter operators are available.

• equals not: all scalar fields.
• in notIn: all scalar fields
• contains startsWith endsWith: String fields
• lt lte gt gte: String, Int, BigInt, Float, Decimal, and Date fields

A filter object can contain multiple field filters, and they are combined with AND semantic. You can also use the AND, OR, and NOT logical operators to combine filter objects to form a complex filter.

Interactive Sample

Click here to pop out if the embed doesn't load an interactive terminal.

Plain Code

basic.ts

Loading...

Relation filters

Filters can be defined on conditions over relations. For one-to-one relations, you can filter on their fields directly. For one-to-many relations, use the "some", "every", or "none" operators to build a condition over a list of records.

Interactive Sample

Click here to pop out if the embed doesn't load an interactive terminal.

Plain Code

relation.ts

Loading...

List filters

List fields allow extra filter operators to filter on the list content:

• has: checks if the list contains a specific value.
• hasEvery: checks if the list contains all values in a given array.
• hasSome: checks if the list contains at least one value in a given array.
• isEmpty: checks if the list is empty.

info

List type is only supported for PostgreSQL.

model Post {
  ...
  topics String[]
}

await db.post.findMany({ 
  where: { topics: { has: 'webdev' } } 
});

await db.post.findMany({
  where: { topics: { hasSome: ['webdev', 'typescript'] } } 
});

await db.post.findMany({
  where: { topics: { hasEvery: ['webdev', 'typescript'] } } 
});

await db.post.findMany({
  where: { topics: { hasNone: ['webdev', 'typescript'] } } 
});

await db.post.findMany({
  where: { topics: { isEmpty: true } } 
});

Json filters

WORK IN PROGRESS

Filtering on Json fields is work in progress and will be available soon.

Query builder filters

ZenStack v3 is implemented on top of Kysely, and it leverages Kysely's powerful query builder API to extend the filtering capabilities. You can use the $expr operator to define a boolean expression that can express almost everything that can be expressed in SQL.

The $expr operator can be used together with other filter operators, so you can keep most of your filters simple and only reach to the query builder level for complicated components.

Interactive Sample

Click here to pop out if the embed doesn't load an interactive terminal.

Plain Code

query-builder.ts

Loading...