Filtering and Sorting
Prisma Client supports filtering with the where
query option, and sorting with the orderBy
query option.
Filtering
Prisma Client allows you to filter records on any combination of model fields, including related models, and supports a variety of filter conditions.
Some filter conditions use the SQL operators LIKE
and ILIKE
which may cause unexpected behavior in your queries. Please refer to our 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)
- an email address that ends with
- Returns all
User
fields - Includes all related
Post
records wherepublished
equalstrue
const result = await prisma.user.findMany({
where: {
email: {
endsWith: 'prisma.io',
},
posts: {
some: {
published: true,
},
},
},
include: {
posts: {
where: {
published: true,
},
},
},
})
Filter conditions and operators
Refer to Prisma Client's reference documentation for a full list of operators , such as startsWith
and contains
.
Combining operators
You can use operators (such as NOT
and OR
) to filter by a combination of conditions. The following query returns all users with an email
that ends in "prisma.io"
or "gmail.com"
, but not "hotmail.com"
:
const result = await prisma.user.findMany({
where: {
OR: [
{
email: {
endsWith: 'prisma.io',
},
},
{ email: { endsWith: 'gmail.com' } },
],
NOT: {
email: {
endsWith: 'hotmail.com',
},
},
},
select: {
email: true,
},
})
Filter on null fields
The following query returns all posts whose content
field is null
:
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
:
const posts = await prisma.post.findMany({
where: {
content: { not: null },
},
})
Filter on relations
Prisma Client supports filtering on related records. For example, in the following schema, a user can have many blog posts:
model User {
id Int @id @default(autoincrement())
name String?
email String @unique
posts Post[] // User can have many posts
}
model Post {
id Int @id @default(autoincrement())
title String
published Boolean @default(true)
author User @relation(fields: [authorId], references: [id])
authorId Int
}
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:
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"
:
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 - for example, the following query returns all posts where the tags
array contains databases
:
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. 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
:
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
Filtering FAQs
How does filtering work at the database level?
For MySQL and PostgreSQL, Prisma Client utilizes the LIKE
(and ILIKE
) 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:
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:
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
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
:
const usersWithPosts = await prisma.user.findMany({
orderBy: [
{
role: 'desc',
},
{
name: 'desc',
},
],
include: {
posts: {
orderBy: {
title: 'desc',
},
select: {
title: true,
},
},
},
})
Note: You can also sort lists of nested records 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:
const posts = await prisma.post.findMany({
orderBy: {
author: {
email: 'asc',
},
},
})
Sort by relation aggregate value
In 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:
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.
Sort by relevance (PostgreSQL and MySQL)
In 3.5.0+ for PostgreSQL and 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 and the MySQL documentation.
Enable order by relevance with the fullTextSearch
preview feature:
generator client {
provider = "prisma-client-js"
previewFeatures = ["fullTextSearch"]
}
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 order the list of users by relevance to the search term 'developer'
in their bio, and search
to filter the list:
const getUsersByRelevance = await prisma.user.findMany({
take: 10,
orderBy: {
_relevance: {
fields: ['bio'],
search: 'developer',
sort: 'asc',
},
},
})
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
This feature is generally available in version 4.16.0
and later. To use this feature in versions 4.1.0
to 4.15.0
the Preview feature orderByNulls
will need to be enabled.
Note: Prisma Client does not support this feature for MongoDB.
You can sort the results so that records with null
fields appear either first or last.
Note: You can only sort by nulls on optional scalar fields. If you try to sort by nulls on a required or relation field, Prisma Client throws a P2009 error.
Example: If updatedAt
is an optional field, then the following query sorts posts by updatedAt
, with null records at the end:
const posts = await prisma.post.findMany({
orderBy: {
updatedAt: { sort: 'asc', nulls: 'last' },
},
})
Sorting FAQs
Can I perform case-insensitive sorting?
Follow issue #841 on GitHub.