Filtering and sorting

Learn how to filter Prisma Client queries with where and sort results with orderBy.

Prisma Client lets you narrow results with where and order them with orderBy.

Filtering with where

Use where to match records by field values:

const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: "prisma.io",
    },
  },
});

Combining operators

You can compose filters with operators such as OR, AND, and NOT:

const users = await prisma.user.findMany({
  where: {
    OR: [
      { email: { endsWith: "gmail.com" } },
      { email: { endsWith: "company.com" } },
    ],
    NOT: {
      email: {
        endsWith: "admin.company.com",
      },
    },
  },
});

Relation filters let you match records based on related data:

const users = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        published: true,
      },
    },
  },
});

For more relation-specific patterns, see Relation queries.

Sort results with orderBy

Use orderBy to control result ordering:

const posts = await prisma.post.findMany({
  orderBy: {
    title: "asc",
  },
});

You can also combine filtering and sorting:

const posts = await prisma.post.findMany({
  where: {
    published: true,
  },
  orderBy: {
    createdAt: "desc",
  },
});

Case sensitivity depends on your database provider and collation settings. For PostgreSQL, Prisma Client also supports specific case-insensitive filter modes on supported operators. See the Prisma Client API reference for details.

Sort by relation

You can sort by properties on related records when the query shape supports it. For example, you might order posts by their author's name or users by related aggregates.

Sort by relevance (PostgreSQL and MySQL)

On supported databases, Prisma Client can sort search results by relevance using _relevance. This is especially useful when combined with full-text search.

Sort with null records first or last

Prisma Client supports explicit null ordering on supported databases so you can keep incomplete values grouped at the beginning or end of a result set.