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