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.

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.

Click here to open an interactive playground.

filter/basic.ts

import { createClient } from '../db';
import { createUsersAndPosts } from '../utils';

async function main() {
  const db = await createClient();
  await createUsersAndPosts(db);

  // value filters
  console.log('Post title is "Post1"');
  console.log(await db.post.findFirst({ where: { title: 'Post1' } }));
  
  // equality filters (equivalent to a value filter)
  console.log('Post title equals "Post1"');
  console.log(await db.post.findFirst({ where: { title: { equals: 'Post1' } } }));

  // string operators
  console.log('Post content starts with "Another"');
  console.log(
    await db.post.findFirst({ where: { content: { startsWith: 'Another' }}})
  );

  // numeric operators
  console.log('Post with viewCount > 1');
  console.log(await db.post.findFirst({ where: { viewCount: { gt: 1 } } }));

  // use "not" to negate a filter
  console.log('Post with not(viewCount > 1)');
  console.log(
    await db.post.findFirst({ 
      where: { viewCount: { not: { gt: 1 } } } 
    })
  );

  // multile fields in a filter object has AND semantic
  console.log('Post with viewCount > 1 && title = "Post1"')
  console.log(
    await db.post.findFirst({ where: { viewCount: { gt: 1 }, title: 'Post1' } })
  );

  // use "in"/"notIn" to check if a field matches any item in a list
  console.log('Post with title in ["Post1, "Post2"]');
  console.log(
    await db.post.findFirst({ where: { title: { in: ['Post1', 'Post2' ] } } })
  );

  // use AND/OR/NOT to build composite filters
  console.log('Post with: viewCount > 1 || (content startsWith "Another" && title != "Post1")')
  console.log(
    await db.post.findFirst({
      where: {
        OR: [
          { viewCount: { gt: 1 } },
          {
            AND: [
              { content: { startsWith: 'Another' } },
              { NOT: { title: 'Post1' } }
            ]
          }
        ]
      }
    })
  )
}

main();

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: { isEmpty: true } } 
});

Json filters

WORK IN PROGRESS

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

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.

Click here to open an interactive playground.

filter/relation.ts

import { createClient } from '../db';
import { createUsersAndPosts } from '../utils';

async function main() {
  const db = await createClient();
  await createUsersAndPosts(db);

  // filter by a one-to-one relation
  console.log('Post owned by u1');
  console.log(await db.post.findFirst({
    where: { author: { email: 'u1@test.com' } }
  }));

  // for optional relation, you can use null check to filter on if the relation 
  // is connected
  console.log('Post not owned by anyone');
  console.log(await db.post.findFirst({
    where: { author: null }
  }));

  // filter by a one-to-many relation using "some", "every", or "none" operator
  console.log('User with at least one published post');
  console.log(await db.user.findFirst({
    where: { posts: { some: { published: true } } }
  }));
}

main();

Query builder filters

🔋 ZenStack vs Prisma

The ability to mix SQL query builder into ORM filters is a major improvement over Prisma.

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.

Click here to open an interactive playground.

filter/query-builder.ts

import { createClient } from '../db';
import { createUsersAndPosts } from '../utils';

async function main() {
  const db = await createClient();
  await createUsersAndPosts(db);

  console.log('Find users with at least two posts');
  console.log(
    await db.user.findMany({
      where: {
        $expr: (eb) =>
          // SELECT (COUNT(*) >= 2) FROM "Post" WHERE "Post"."id" = "User"."id"
          eb
            .selectFrom('Post')
            .whereRef('Post.authorId', '=', 'User.id')
            .select(({fn}) => eb(fn.countAll(), '>=', 2).as('hasMorePosts'))
      }
    })
  );
}

main();