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:

1const usersWithPosts = await prisma.user.findMany({
2 orderBy: {
3 role: "desc",
4 email: "desc",
5 },
6 include: {
7 posts: {
8 orderBy: {
9 title: "desc",
10 },
11 select: {
12 title: true,
13 },
14 },
15 },
16});

Note: The ability to sort by multiple fields is available in version 2.4.0 and later.

1[
2 {
3 "email": "kwame@prisma.io",
4 "id": 2,
5 "name": "Kwame",
6 "role": "USER",
7 "posts": [
8 {
9 "title": "Prisma in five minutes"
10 },
11 {
12 "title": "Happy Table Friends: Relations in Prisma"
13 }
14 ]
15 },
16 {
17 "email": "emily@prisma.io",
18 "id": 5,
19 "name": "Emily",
20 "role": "USER",
21 "posts": [
22 {
23 "title": "Prisma Day 2020"
24 },
25 {
26 "title": "My first day at Prisma"
27 },
28 {
29 "title": "All about databases"
30 }
31 ]
32 }
33]

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

  • String
  • Int
  • Float
  • Boolean
  • DateTime

The following queries support sorting:

  • findMany
  • aggregate (preview)

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:

1export type PostOrderByInput = {
2 authorId?: SortOrder
3 content?: SortOrder
4 id?: SortOrder
5 published?: SortOrder
6 title?: SortOrder
7}
8
9export type UserOrderByInput = {
10 email?: SortOrder
11 id?: SortOrder
12 name?: SortOrder
13 extendedProfile?: SortOrder
14 role?: SortOrder
15}
16
17export declare const SortOrder: {
18 asc: 'asc',
19 desc: 'desc'
20};
21
22export declare type SortOrder = (typeof SortOrder)[keyof typeof SortOrder]
1 generator client {
2 provider = "prisma-client-js"
3}
4
5datasource db {
6 provider = "mysql"
7 url = env("DATABASE_URL")
8}
9
10model Post {
11 authorId Int?
12 content String?
13 id Int @default(autoincrement()) @id
14 published Boolean @default(false)
15 title String
16 user User? @relation(fields: [authorId], references: [id])
17
18 @@index([authorId], name: "authorId")
19}
20
21model User {
22 email String @unique
23 id Int @default(autoincrement()) @id
24 name String?
25 posts Post[]
26 extendedProfile Json?
27 role Role @default(USER)
28}
29
30enum Role {
31 ADMIN
32 USER
33 MODERATOR
34}

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 #690 on GitHub.

Examples

Sort User by email field

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

1const users = await prisma.user.findMany({
2 orderBy: {
3 email: 'asc',
4 },
5})

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

1const users = await prisma.user.findMany({
2 orderBy: {
3 email: 'desc',
4 },
5})

Sort User by multiple fields - email and role

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

1const users = await prisma.user.findMany({
2 select: {
3 email: true,
4 role: true,
5 },
6 orderBy: {
7 email: "desc",
8 role: "desc",
9 },
10});
1[
2 {
3 "email": "yuki@prisma.io",
4 "role": "USER"
5 },
6 {
7 "email": "nora@prisma.io",
8 "role": "USER"
9 },
10 {
11 "email": "mary@prisma.io",
12 "role": "MODERATOR"
13 },
14 {
15 "email": "elsa@prisma.io",
16 "role": "MODERATOR"
17 },
18 {
19 "email": "eloise@prisma.io",
20 "role": "USER"
21 },
22 {
23 "email": "coco@prisma.io",
24 "role": "ADMIN"
25 },
26 {
27 "email": "anna@prisma.io",
28 "role": "USER"
29 },
30 {
31 "email": "alice@prisma.io",
32 "role": "USER"
33 }
34]

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

1const users = await prisma.user.findMany({
2 select: {
3 email: true,
4 role: true,
5 },
6 orderBy: {
7 role: "desc",
8 email: "desc",
9 },
10});
1[
2 {
3 "email": "mary@prisma.io",
4 "role": "MODERATOR"
5 },
6 {
7 "email": "elsa@prisma.io",
8 "role": "MODERATOR"
9 },
10 {
11 "email": "yuki@prisma.io",
12 "role": "USER"
13 },
14 {
15 "email": "nora@prisma.io",
16 "role": "USER"
17 },
18 {
19 "email": "eloise@prisma.io",
20 "role": "USER"
21 },
22 {
23 "email": "anna@prisma.io",
24 "role": "USER"
25 },
26 {
27 "email": "alice@prisma.io",
28 "role": "USER"
29 },
30 {
31 "email": "coco@prisma.io",
32 "role": "ADMIN"
33 }
34]

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:

1const users3 = await prisma.user.findMany({
2 orderBy: {
3 email: 'asc',
4 },
5 select: {
6 name: true,
7 email: true,
8 },
9})

Example output:

1[
2 {
3 "name": "Alice",
4 "email": "alice@prisma.io"
5 },
6 {
7 "name": "Ariadne",
8 "email": "ariadne@prisma.io"
9 },
10 {
11 "name": "Bob",
12 "email": "bob@prisma.io"
13 }
14]

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
1const usersWithPosts = await prisma.user.findMany({
2 orderBy: {
3 email: 'asc',
4 },
5 include: {
6 posts: {
7 select: {
8 title: true,
9 },
10 orderBy: {
11 title: 'asc',
12 },
13 },
14 },
15})

Example output:

1[
2 {
3 "id": 2,
4 "email": "alice@prisma.io",
5 "name": "Alice",
6 "posts": [
7 {
8 "title": "Watch the talks from Prisma Day 2019"
9 }
10 ]
11 },
12 {
13 "id": 3,
14 "email": "ariadne@prisma.io",
15 "name": "Ariadne",
16 "posts": [
17 {
18 "title": "How to connect to a SQLite database"
19 },
20 {
21 "title": "My first day at Prisma"
22 }
23 ]
24 },
25 {
26 "id": 1,
27 "email": "bob@prisma.io",
28 "name": "Bob",
29 "posts": [
30 {
31 "title": "Follow Prisma on Twitter"
32 },
33 {
34 "title": "Subscribe to GraphQL Weekly for community news "
35 }
36 ]
37 }
38]

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:

1const userWithPosts = await prisma.user.findOne({
2 where: {
3 id: 1,
4 },
5 include: {
6 posts: {
7 orderBy: {
8 title: "desc",
9 },
10 select: {
11 title: true,
12 published: true,
13 },
14 },
15 },
16});

Example output:

1{
2 "email": "sarah@prisma.io",
3 "id": 1,
4 "name": "Sarah",
5 "extendedProfile": null,
6 "role": "USER",
7 "posts": [
8 {
9 "title": "Prisma Day 2020",
10 "published": false
11 },
12 {
13 "title": "My first post",
14 "published": false
15 },
16 {
17 "title": "All about databases",
18 "published": true
19 }
20 ]
21}

Sort by enum

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

1const sort = await prisma.user.findMany({
2 orderBy: {
3 role: 'desc',
4 },
5 select: {
6 email: true,
7 role: true,
8 },
9})

Example output:

1[
2 {
3 "email": "emma@prisma.io",
4
5 "role": "USER"
6 },
7 {
8 "email": "suma@prisma.io",
9 "role": "ADMIN"
10 },
11 {
12 "email": "kwame@prisma.io",
13 "role": "ADMIN"
14 },
15 {
16 "email": "pearl@prisma.io",
17 "role": "ADMIN"
18 }
19]
Edit this page on Github