# Filtering and Sorting (/docs/orm/v6/prisma-client/queries/filtering-and-sorting)
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 [#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 [#filter-conditions-and-operators]
Refer to Prisma Client's reference documentation for [a full list of operators](/orm/v6/reference/prisma-client-reference#filter-conditions-and-operators) , such as `startsWith` and `contains`.
Combining operators [#combining-operators]
You can use operators (such as [`NOT`](/orm/v6/reference/prisma-client-reference#not-1) and [`OR`](/orm/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 [#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 [#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 [#filter-on-relations]
Prisma Client supports [filtering on related records](/orm/v6/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
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 [#filter-on-scalar-lists--arrays]
Scalar lists (for example, `String[]`) have a special set of [filter conditions](/orm/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]
Case-insensitive filtering [is available as a feature for the PostgreSQL and MongoDB providers](/orm/v6/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/v6/prisma-client/queries/case-sensitivity)
Filtering FAQs [#filtering-faqs]
How does filtering work at the database level? [#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 [#sorting]
Use [`orderBy`](/orm/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](/orm/v6/prisma-client/queries/relation-queries#filter-a-list-of-relations)
> to retrieve a single record by ID.
Sort by relation [#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 [#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) [#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/v6/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",
},
},
});
```
> [!NOTE]
> Prior to Prisma ORM 5.16.0, enabling the `fullTextSearch` preview feature would rename the `OrderByWithRelationInput` TypeScript types to `OrderByWithRelationAndSearchRelevanceInput`. If you are using the Preview feature, you will need to update your type imports.
Sort with null records first or last [#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/prisma/releases/tag/4.1.0) to [`4.15.0`](https://github.com/prisma/prisma/releases/tag/4.15.0) the [Preview feature](/orm/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](/orm/v6/prisma-schema/data-model/models#scalar-fields) fields. If you try to sort by nulls on a required or [relation](/orm/v6/prisma-schema/data-model/models#relation-fields) field, Prisma Client throws a [P2009 error](/orm/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 [#sorting-faqs]
Can I perform case-insensitive sorting? [#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`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/aggregation-grouping-summarizing): Use Prisma Client to aggregate, group by, count, and select distinct.
- [`Case sensitivity`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/case-sensitivity): How Prisma Client handles case sensitivity when filtering and sorting.
- [`Computed fields`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/computed-fields): This page explains how to use client extensions to add computed fields to Prisma models.
- [`CRUD`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/crud): How to perform CRUD with Prisma Client.
- [`Custom models`](https://www.prisma.io/docs/orm/v6/prisma-client/queries/custom-models): This page explains how to wrap Prisma Client in custom models