Filtering and sorting (Prisma ORM v7) (/docs/orm/v7/prisma-client/queries/filtering-and-sorting)

For the complete Prisma documentation index, see llms.txt. A markdown version of any docs page is available by appending .md to its URL.

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

Location: ORM > v7 > Prisma Client > Queries > Filtering and sorting

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

Use where to match records by field values:

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

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.

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.

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.

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

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.

• Pagination
• Select fields
• Prisma Client API reference

• Aggregation, grouping, and summarizing: Use Prisma Client to aggregate, group by, count, and select distinct.
• CRUD: Learn how to perform create, read, update, and delete operations
• Excluding fields: Learn how to exclude fields from Prisma Client results with the omit option.
• Full-text search: Learn how to search text fields with Prisma Client using your database's native full-text search support.
• Pagination: Learn how to paginate Prisma Client query results with offset pagination and cursor-based pagination.