Filtering and Sorting

Prisma Dart Client supports filtering with the where query option, and sorting with the orderBy query option.

Filtering

Prisma Client allows you to filter records on any combination of model fields, including related models, and supports a variety of filter conditions.

The following query:

• Returns all User records with
  • an email address that ends with odore.com
  • at least one published post (a relation query)
• Returns all User fields
• Includes all related Post records where published equals true

await prisma.user.findMany(
  where: UserWhereInput(
    email: PrismaUnion.$1(
      StringFilter(endsWith: PrismaUnion.$1('@odroe.com')),
    ),
    posts: PostListRelationFilter(
      some: PostWhereInput(
        published: PrismaUnion.$2(true),
      ),
    ),
  ),
  include: UserInclude(
    posts: PrismaUnion.$2(
      UserPostsArgs(
        where: PostWhereInput(
          published: PrismaUnion.$2(true),
        ),
      ),
    ),
  ),
);

Filter on null fields

The following query returns all posts whose content field is null:

await prisma.post.findMany(
  where: PostWhereInput(
    content: PrismaUnion.$2(
      PrismaUnion.$2(const PrismaNull()),
    ),
  ),
);

Filter for non-null fields

The following query returns all posts whose content field is not null:

await prisma.post.findMany(
  where: PostWhereInput(
    content: PrismaUnion.$1(
      StringNullableFilter(
        not: PrismaUnion.$2(
          PrismaUnion.$2(const PrismaNull()),
        ),
      ),
    ),
  ),
);

Filter on relations

Prisma Client supports filtering on related records. For example, in the following schema, a user can have many blog posts:

model User {
  id    Int     @id @default(autoincrement())
  name  String?
  email String  @unique
  posts Post[] // User can have many posts
}

model Post {
  id        Int     @id @default(autoincrement())
  title     String
  published Boolean @default(true)
  author    User    @relation(fields: [authorId], references: [id]) 
  authorId  Int
}

The one-to-many relation between User and Post allows you to query users based on their posts - for example, the following query returns all users where at least one post (some) has more than 10 views:

await prisma.user.findMany(
  where: UserWhereInput(
    posts: PostListRelationFilter(
      some: PostWhereInput(
        views: PrismaUnion.$1(
          IntFilter(gt: PrismaUnion.$1(10)),
        ),
      ),
    ),
  ),
);

You can also query posts based on the properties of the author. For example, the following query returns all posts where the author's email contains odroe.com:

await prisma.post.findMany(
  where: PostWhereInput(
    author: PrismaUnion.$2(
      PrismaUnion.$1(
        UserWhereInput(
          email: PrismaUnion.$1(
            StringFilter(contains: PrismaUnion.$1('odroe.com')),
          ),
        ),
      ),
    ),
  ),
);

Filter on scalar lists / arrays

Scalar lists (for example String[]) have a special set of filter conditions - for example, the following query returns all posts where the tags array contains databases:

await prisma.post.findMany(
  where: PostWhereInput(
    tags: StringNullableListFilter(
      has: PrismaUnion.$1('databases'), 
    ),
  ),
);

Case-insensitive filtering

Case-insensitive filtering is available as a feature for the PostgreSQL and MongoDB providers. MySQL, MariaDB and Microsoft SQL Server are case-insensitive by default, and do not require a Prisma Client feature to make case-insensitive filtering possible.

To use case-insensitive filtering, add the mode property to a particular filter and specify insensitive:

await prisma.user.findMany(
  where: UserWhereInput(
    email: PrismaUnion.$1(
      StringFilter(
        endsWith: PrismaUnion.$1('@odroe.com'),
        mode: QueryMode.insensitive, 
      ),
    ),
    name: PrismaUnion.$1(
      StringNullableFilter(
        equals:
            PrismaUnion.$1('Seven Odroe'), // Default mode
      ),
    ),
  ),
);

More see 👉 Case Insensitive official docs.

Sorting

Use orderBy to sort a list of records or a nested list of records by a particular field or set of fields. For example, the following query returns all User records sorted by role and name, and each user's posts sorted by title:

await prisma.user.findMany(
  orderBy: PrismaUnion.$1([
    UserOrderByWithRelationInput(role: SortOrder.desc),
    UserOrderByWithRelationInput(
      name: PrismaUnion.$1(SortOrder.desc),
    ),
  ]),
  include: UserInclude(
    posts: PrismaUnion.$2(
      UserPostsArgs(
        orderBy: PrismaUnion.$2(
          PostOrderByWithRelationInput(title: SortOrder.desc),
        ),
        select: PostSelect(title: true),
      ),
    ),
  ),
);

Sort by relation

You can also sort by properties of a relation. For example, the following query sorts all posts by the author's email address:

await prisma.post.findMany(
  orderBy: PrismaUnion.$2(
    PostOrderByWithRelationInput(
      author: UserOrderByWithRelationInput(
        email: SortOrder.asc,
      ),
    ),
  ),
);

Sort by relation aggregate value

you can sort by the count of related records, For example, the following query sorts users by the number of related posts:

await prisma.user.findMany(
  take: 10,
  orderBy: PrismaUnion.$2(
    UserOrderByWithRelationInput(
      posts: PostOrderByRelationAggregateInput(
        $count: SortOrder.desc,
      ),
    ),
  ),
);