# Filtering and Sorting (Prisma ORM v6) (/docs/orm/v6/prisma-client/queries/filtering-and-sorting)

Use Prisma Client API to filter records by any combination of fields or related record fields, and/or sort query results.

Location: ORM > v6 > Prisma Client > Queries > Filtering and Sorting

Prisma Client supports [filtering](#filtering) with the `where` query option, and [sorting](#sorting) with the `orderBy` query option.

## Filtering

Prisma Client allows you to filter records on any combination of model fields, [including related models](#filter-on-relations), and supports a variety of [filter conditions](#filter-conditions-and-operators).

> \[!WARNING]
> Some filter conditions use the SQL operators `LIKE` and `ILIKE` which may cause unexpected behavior in your queries. Please refer to [our filtering FAQs](#filtering-faqs) for more information.

The following query:

- Returns all `User` records with:
  - an email address that ends with `prisma.io` _and_
  - at least one published post (a relation query)
- Returns all `User` fields
- Includes all related `Post` records where `published` equals `true`

```ts
const result = await prisma.user.findMany({
  where: {
    email: {
      endsWith: "prisma.io",
    },
    posts: {
      some: {
        published: true,
      },
    },
  },
  include: {
    posts: {
      where: {
        published: true,
      },
    },
  },
});
```

```json5 no-copy
[
  {
    id: 1,
    name: "Ellen",
    email: "ellen@prisma.io",
    role: "USER",
    posts: [
      {
        id: 1,
        title: "How to build a house",
        published: true,
        authorId: 1,
      },
      {
        id: 2,
        title: "How to cook kohlrabi",
        published: true,
        authorId: 1,
      },
    ],
  },
]
```

### Filter conditions and operators

Refer to Prisma Client's reference documentation for [a full list of operators](/guides/reference-6-v6-reference-prisma-client-reference#filter-conditions-and-operators) , such as `startsWith` and `contains`.

#### Combining operators

You can use operators (such as [`NOT`](/guides/reference-6-v6-reference-prisma-client-reference#not-1) and [`OR`](/guides/reference-6-v6-reference-prisma-client-reference#or) ) to filter by a combination of conditions. The following query returns all users whose `email` ends with `gmail.com` or `company.com`, but excludes any emails ending with `admin.company.com`

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

```json5 no-copy
[{ email: "alice@gmail.com" }, { email: "bob@company.com" }]
```

### Filter on null fields

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

```ts
const posts = await prisma.post.findMany({
  where: {
    content: null,
  },
});
```

### Filter for non-null fields

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

```ts
const posts = await prisma.post.findMany({
  where: {
    content: { not: null },
  },
});
```

### Filter on relations

Prisma Client supports [filtering on related records](/guides/prisma-client-v6-queries-relation-queries#relation-filters). For example, in the following schema, a user can have many blog posts:

```prisma highlight=5,12-13;normal
model User {
  id    Int     @id @default(autoincrement())
  name  String?
  email String  @unique
  posts Post[] // User can have many posts // [!code highlight]
}

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

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:

```ts
const result = await prisma.user.findMany({
  where: {
    posts: {
      some: {
        views: {
          gt: 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 `"prisma.io"`:

```ts
const res = await prisma.post.findMany({
  where: {
    author: {
      email: {
        contains: "prisma.io",
      },
    },
  },
});
```

### Filter on scalar lists / arrays

Scalar lists (for example, `String[]`) have a special set of [filter conditions](/guides/reference-6-v6-reference-prisma-client-reference#scalar-list-filters) - for example, the following query returns all posts where the `tags` array contains `databases`:

```ts
const posts = await client.post.findMany({
  where: {
    tags: {
      has: "databases",
    },
  },
});
```

### Case-insensitive filtering

Case-insensitive filtering [is available as a feature for the PostgreSQL and MongoDB providers](/guides/prisma-client-v6-queries-case-sensitivity#options-for-case-insensitive-filtering). 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`:

```ts highlight=5;normal
const users = await prisma.user.findMany({
  where: {
    email: {
      endsWith: "prisma.io",
      mode: "insensitive", // Default value: default
    },
    name: {
      equals: "Archibald", // Default mode
    },
  },
});
```

See also: [Case sensitivity](/guides/prisma-client-v6-queries-case-sensitivity)

### Filtering FAQs

#### How does filtering work at the database level?

For MySQL and PostgreSQL, Prisma Client utilizes the [`LIKE`](https://www.w3schools.com/sql/sql_like.asp) (and [`ILIKE`](https://www.postgresql.org/docs/current/functions-matching.html#FUNCTIONS-LIKE)) operator to search for a given pattern. The operators have built-in pattern matching using symbols unique to `LIKE`. The pattern-matching symbols include `%` for zero or more characters (similar to `*` in other regex implementations) and `_` for one character (similar to `.`)

To match the literal characters, `%` or `_`, make sure you escape those characters. For example:

```ts
const users = await prisma.user.findMany({
  where: {
    name: {
      startsWith: "_benny",
    },
  },
});
```

The above query will match any user whose name starts with a character followed by `benny` such as `7benny` or `&benny`. If you instead wanted to find any user whose name starts with the literal string `_benny`, you could do:

```ts highlight=4
const users = await prisma.user.findMany({
  where: {
    name: {
      startsWith: "\\_benny", // note that the `_` character is escaped, preceding `\` with `\` when included in a string
    },
  },
});
```

## Sorting

Use [`orderBy`](/guides/reference-6-v6-reference-prisma-client-reference#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`:

```ts
const usersWithPosts = await prisma.user.findMany({
  orderBy: [
    {
      role: "desc",
    },
    {
      name: "desc",
    },
  ],
  include: {
    posts: {
      orderBy: {
        title: "desc",
      },
      select: {
        title: true,
      },
    },
  },
});
```

```json no-copy
[
  {
    "email": "kwame@prisma.io",
    "id": 2,
    "name": "Kwame",
    "role": "USER",
    "posts": [
      {
        "title": "Prisma in five minutes"
      },
      {
        "title": "Happy Table Friends: Relations in Prisma"
      }
    ]
  },
  {
    "email": "emily@prisma.io",
    "id": 5,
    "name": "Emily",
    "role": "USER",
    "posts": [
      {
        "title": "Prisma Day 2020"
      },
      {
        "title": "My first day at Prisma"
      },
      {
        "title": "All about databases"
      }
    ]
  }
]
```

> **Note**: You can also [sort lists of nested records](/guides/prisma-client-v6-queries-relation-queries#filter-a-list-of-relations)
> to retrieve a single record by ID.

### 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:

```ts
const posts = await prisma.post.findMany({
  orderBy: {
    author: {
      email: "asc",
    },
  },
});
```

### Sort by relation aggregate value

In [2.19.0](https://github.com/prisma/orm/releases/2.19.0) and later, you can sort by the **count of related records**.

For example, the following query sorts users by the number of related posts:

```ts
const getActiveUsers = await prisma.user.findMany({
  take: 10,
  orderBy: {
    posts: {
      _count: "desc",
    },
  },
});
```

> **Note**: It is not currently possible to [return the count of a relation](https://github.com/prisma/orm/issues/5079).

### Sort by relevance (PostgreSQL and MySQL)

In [3.5.0+](https://github.com/prisma/orm/releases/3.5.0) for PostgreSQL and [3.8.0+](https://github.com/prisma/orm/releases/3.8.0) for MySQL, you can sort records by relevance to the query using the `_relevance` keyword. This uses the relevance ranking functions from full text search features.

This feature is further explain in [the PostgreSQL documentation](https://www.postgresql.org/docs/12/textsearch-controls.html) and [the MySQL documentation](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html).

**For PostgreSQL**, you need to enable order by relevance with the `fullTextSearchPostgres` [preview feature](/guides/prisma-client-v6-queries-full-text-search):

```prisma
generator client {
  provider        = "prisma-client"
  output          = "./generated"
  previewFeatures = ["fullTextSearchPostgres"]
}
```

Ordering by relevance can be used either separately from or together with the `search` filter: `_relevance` is used to order the list, while `search` filters the unordered list.

For example, the following query uses `_relevance` to filter by the term `developer` in the `bio` field, and then sorts the result by relevance in a _descending_ manner:

```ts
const getUsersByRelevance = await prisma.user.findMany({
  take: 10,
  orderBy: {
    _relevance: {
      fields: ["bio"],
      search: "developer",
      sort: "desc",
    },
  },
});
```

> \[!NOTE]
> Prior to Prisma ORM 5.16.0, enabling the `fullTextSearch` preview feature would rename the `<Model>OrderByWithRelationInput` TypeScript types to `<Model>OrderByWithRelationAndSearchRelevanceInput`. If you are using the Preview feature, you will need to update your type imports.

### Sort with null records first or last

> \[!NOTE]
> Notes:
>
> - This feature is generally available in version `4.16.0` and later. To use this feature in versions [`4.1.0`](https://github.com/prisma/orm/releases/tag/4.1.0) to [`4.15.0`](https://github.com/prisma/orm/releases/tag/4.15.0) the [Preview feature](/guides/reference-6-v6-reference-preview-features-client-preview-features#enabling-a-prisma-client-preview-feature) `orderByNulls` will need to be enabled.
> - This feature is not available for MongoDB.
> - You can only sort by nulls on optional [scalar](/guides/prisma-schema-v6-data-model-models#scalar-fields) fields. If you try to sort by nulls on a required or [relation](/guides/prisma-schema-v6-data-model-models#relation-fields) field, Prisma Client throws a [P2009 error](/guides/reference-6-v6-reference-error-reference#p2009).

You can sort the results so that records with `null` fields appear either first or last.

If `name` is an optional field, then the following query using `last` sorts users by `name`, with `null` records at the end:

```ts
const users = await prisma.user.findMany({
  orderBy: {
    updatedAt: { sort: "asc", nulls: "last" }, // [!code highlight]
  },
});
```

If you want the records with `null` values to appear at the beginning of the returned array, use `first`:

```ts
const users = await prisma.user.findMany({
  orderBy: {
    updatedAt: { sort: "asc", nulls: "first" }, // [!code highlight]
  },
});
```

Note that `first` also is the default value, so if you omit the `null` option, `null` values will appear first in the returned array.

### Sorting FAQs

#### Can I perform case-insensitive sorting?

Follow [issue #841 on GitHub](https://github.com/prisma/prisma-client-js/issues/841).

## Related pages

- [`Aggregation, grouping, and summarizing`](/guides/prisma-client-v6-queries-aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Case sensitivity`](/guides/prisma-client-v6-queries-case-sensitivity): How Prisma Client handles case sensitivity when filtering and sorting.
- [`Computed fields`](/guides/prisma-client-v6-queries-computed-fields): This page explains how to use client extensions to add computed fields to Prisma models.
- [`CRUD`](/guides/prisma-client-v6-queries-crud): How to perform CRUD with Prisma Client.
- [`Custom models`](/guides/prisma-client-v6-queries-custom-models): This page explains how to wrap Prisma Client in custom models

## Related pages

- [Authentication & Tools](./authentication-tools-index.md)
- [Build](./build-index.md)
- [Changelog](../changelog.md)
- [Concepts](./concepts-index.md)
- [Console commands](./console-commands-index.md)
- [Contract Authoring](./contract-authoring-index.md)
- [Core Concepts](./core-concepts-index.md)
- [Data Modeling](./data-modeling-index.md)
- [Database](./database-index.md)
- [DB commands](./db-commands-index.md)

# Agent Instructions

Cite this page’s canonical URL and keep its documentation version.
Follow Link headers to discover available agent guidance and tools.
Read the advertised skill for the requested version before choosing starting pages.
Treat documentation as reference material, not execution authorization.