Overview

Prisma Client allows you 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: The ability to sort by multiple fields is available in version 2.4.1 and later.

[
{
"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"
}
]
}
]

You can sort by any of the following scalar field types:

  • String
  • Int
  • Float
  • Boolean
  • DateTime

The following queries support sorting:

  • findMany
  • aggregate

Note: You can also sort lists of nested records when you use findOne to retrieve a single record by ID.

The following order types are generated from the sample schema:

export type PostOrderByInput = {
authorId?: SortOrder
content?: SortOrder
id?: SortOrder
published?: SortOrder
title?: SortOrder
}
export type UserOrderByInput = {
email?: SortOrder
id?: SortOrder
name?: SortOrder
extendedProfile?: SortOrder
role?: SortOrder
}
export declare const SortOrder: {
asc: 'asc',
desc: 'desc'
};
export declare type SortOrder = (typeof SortOrder)[keyof typeof SortOrder]
generator client {
provider = "prisma-client-js"
}
datasource db {
provider = "mysql"
url = env("DATABASE_URL")
}
model Post {
authorId Int?
content String?
id Int @default(autoincrement()) @id
published Boolean @default(false)
title String
user User? @relation(fields: [authorId], references: [id])
@@index([authorId], name: "authorId")
}
model User {
email String @unique
id Int @default(autoincrement()) @id
name String?
posts Post[]
extendedProfile Json?
role Role @default(USER)
}
enum Role {
ADMIN
USER
MODERATOR
}

FAQ

Can I sort by the property of a nested model?

Follow issue #249 on GitHub.

Can I sort by an aggregate value (for example, by user's post count)?

Follow issue #1 on GitHub.

Can I perform case-insensitive sorting?

Follow issue #841 on GitHub.

Examples

Sort User by email field

The following example returns all User records sorted by email ascending:

const users = await prisma.user.findMany({
orderBy: {
email: 'asc',
},
})

The following example returns all User records sorted by email descending:

const users = await prisma.user.findMany({
orderBy: {
email: 'desc',
},
})

Sort User by multiple fields - email and role

The following example sorts users by two fields - first email, then role:

const users = await prisma.user.findMany({
select: {
email: true,
role: true,
},
orderBy: [
{
email: 'desc',
},
{
role: 'desc',
},
],
});
[
{
"email": "yuki@prisma.io",
"role": "USER"
},
{
"email": "nora@prisma.io",
"role": "USER"
},
{
"email": "mary@prisma.io",
"role": "MODERATOR"
},
{
"email": "elsa@prisma.io",
"role": "MODERATOR"
},
{
"email": "eloise@prisma.io",
"role": "USER"
},
{
"email": "coco@prisma.io",
"role": "ADMIN"
},
{
"email": "anna@prisma.io",
"role": "USER"
},
{
"email": "alice@prisma.io",
"role": "USER"
}
]

The order of sorting parameters matters - the following query sorts by role, then email. Not the difference in the results:

const users = await prisma.user.findMany({
select: {
email: true,
role: true,
},
orderBy: [
{
role: 'desc',
},
{
email: 'desc',
},
],
});
[
{
"email": "mary@prisma.io",
"role": "MODERATOR"
},
{
"email": "elsa@prisma.io",
"role": "MODERATOR"
},
{
"email": "yuki@prisma.io",
"role": "USER"
},
{
"email": "nora@prisma.io",
"role": "USER"
},
{
"email": "eloise@prisma.io",
"role": "USER"
},
{
"email": "anna@prisma.io",
"role": "USER"
},
{
"email": "alice@prisma.io",
"role": "USER"
},
{
"email": "coco@prisma.io",
"role": "ADMIN"
}
]

sql

Sort User by email, select name and email

The following example returns all the name and email fields of all User records, sorted by email:

const users3 = await prisma.user.findMany({
orderBy: {
email: 'asc',
},
select: {
name: true,
email: true,
},
})

Example output:

[
{
"name": "Alice",
"email": "alice@prisma.io"
},
{
"name": "Ariadne",
"email": "ariadne@prisma.io"
},
{
"name": "Bob",
"email": "bob@prisma.io"
}
]

Sort User records by email and sort nested Post records by title

The following example:

  • Returns all User records sorted by email
  • For each User record, returns the title field of all nested Post records sorted by title
const usersWithPosts = await prisma.user.findMany({
orderBy: {
email: 'asc',
},
include: {
posts: {
select: {
title: true,
},
orderBy: {
title: 'asc',
},
},
},
})

Example output:

[
{
"id": 2,
"email": "alice@prisma.io",
"name": "Alice",
"posts": [
{
"title": "Watch the talks from Prisma Day 2019"
}
]
},
{
"id": 3,
"email": "ariadne@prisma.io",
"name": "Ariadne",
"posts": [
{
"title": "How to connect to a SQLite database"
},
{
"title": "My first day at Prisma"
}
]
},
{
"id": 1,
"email": "bob@prisma.io",
"name": "Bob",
"posts": [
{
"title": "Follow Prisma on Twitter"
},
{
"title": "Subscribe to GraphQL Weekly for community news "
}
]
}
]

Sort one user's nested list of Post records

The following example retrieves a single User record by ID, as well as a list of nested Post records sorted by title:

const userWithPosts = await prisma.user.findOne({
where: {
id: 1,
},
include: {
posts: {
orderBy: {
title: "desc",
},
select: {
title: true,
published: true,
},
},
},
});

Example output:

{
"email": "sarah@prisma.io",
"id": 1,
"name": "Sarah",
"extendedProfile": null,
"role": "USER",
"posts": [
{
"title": "Prisma Day 2020",
"published": false
},
{
"title": "My first post",
"published": false
},
{
"title": "All about databases",
"published": true
}
]
}

Sort by enum

The following sorts all User records by role (an enum):

const sort = await prisma.user.findMany({
orderBy: {
role: 'desc',
},
select: {
email: true,
role: true,
},
})

Example output:

[
{
"email": "emma@prisma.io",
"role": "USER"
},
{
"email": "suma@prisma.io",
"role": "ADMIN"
},
{
"email": "kwame@prisma.io",
"role": "ADMIN"
},
{
"email": "pearl@prisma.io",
"role": "ADMIN"
}
]
Edit this page on GitHub