Filtering and sorting
Prisma Client supports filtering and sorting with the where
and orderBy
query options respectively.
Filtering
Prisma Client allows you to filter records on any combination of model fields, including related models, and supports a variety of filter conditions.
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 the Prisma Client reference documentation for a full list of operators , such as startsWith
and contains
.
Combining operators
You can use (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 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 @uniqueposts Post[] // User can have many posts}model Post {id Int @id @default(autoincrement())title Stringpublished 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 provider. MySQL and MariaDB 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
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',},{email: '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 (Preview)
In 2.16.0 and later, you can sort by properties of a relation. To enable this feature, add orderByRelation
to previewFeatures
in your schema:
generator client {provider = "prisma-client-js"previewFeatures = ["orderByRelation"]}
For example, the following query sorts all posts by the author's email address:
const posts = await prisma.post.findMany({orderBy: {author: {email: "asc"}}})
Sorting FAQs
Can I sort by an aggregate value (for example, by user's post count)?
It is not yet possible to sort by an aggregate value. Follow issue #3821 on GitHub for updates.
Can I perform case-insensitive sorting?
Follow issue #841 on GitHub.