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`

<CodeWithResult>
<cmd>

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

</cmd>
<cmdResult>

```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,
      },
    ],
  },
]
```

</cmdResult>
</CodeWithResult>

### Filter conditions and operators

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

#### Combining operators

You can use operators (such as [`NOT`](/orm/reference/prisma-client-reference#not-1) and [`OR`](/orm/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`

<CodeWithResult>
<cmd>

```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,
  },
})
```

</cmd>
<cmdResult>

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

</cmdResult>
</CodeWithResult>

### 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](/orm/prisma-client/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
  //highlight-next-line
  posts Post[] // User can have many posts
}

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

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](/orm/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](/orm/prisma-client/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](/orm/prisma-client/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`](/orm/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`:

<CodeWithResult>

<cmd>

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

</cmd>

<cmdResult>

```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"
      }
    ]
  }
]
```

</cmdResult>

</CodeWithResult>

> **Note**: You can also [sort lists of nested records](/orm/prisma-client/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/prisma/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/prisma/issues/5079).

### Sort by relevance (PostgreSQL and MySQL)

In [3.5.0+](https://github.com/prisma/prisma/releases/3.5.0) for PostgreSQL and [3.8.0+](https://github.com/prisma/prisma/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](/orm/prisma-client/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',
    },
  },
})
```

<br />

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

:::info

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/prisma/releases/tag/4.1.0) to [`4.15.0`](https://github.com/prisma/prisma/releases/tag/4.15.0) the [Preview feature](/orm/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](/orm/prisma-schema/data-model/models#scalar-fields) fields. If you try to sort by nulls on a required or [relation](/orm/prisma-schema/data-model/models#relation-fields) field, Prisma Client throws a [P2009 error](/orm/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: {
    // highlight-next-line
    updatedAt: { sort: 'asc', nulls: 'last' },
  },
})
```

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: {
    // highlight-next-line
    updatedAt: { sort: 'asc', nulls: 'first' },
  },
})
```

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).

---

# Pagination