Ordering by multiple columns in Prisma

Question

I know currently Prisma doesn't support ordering by multiple scalars fields, (see this issue: https://github.com/prisma/prisma/issues/62). But, I'm wondering if there is someone who found a solution to work around this issue without using executeRaw mutation (raw SQL) because I have many places in my code where I need to order by multiple fields and I don't want to use executeRaw in so many places. I will appreciate any suggestions. Thank you!

score 34 · Accepted Answer · answered Jan 31 '22 at 15:01

34

Since Prisma 2.4 this should be basically possible by using array in orderBy:

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

Find more in docs: https://www.prisma.io/docs/reference/api-reference/prisma-client-reference#sort-user-by-multiple-fields---email-and-role

answered Jan 31 '22 at 15:01

Filip Ibl

557
5
8

Ok, but how do I write `... ORDER BY (foo = 1) DESC, bar ASC, foo ASC, baz ASC;`? – panzi Jul 01 '22 at 16:54
1

What do you mean by (foo = 1) ? – Filip Ibl Oct 19 '22 at 15:43
I want rows where foo equals to 1 be sorted before the rest. In SQL you can do that as I've written it. – panzi Oct 20 '22 at 22:33

score 0 · Answer 2 · answered Apr 30 '19 at 18:23

0

I don't think there's a solution, In my project, I need random order, increment/decrement, aggregation... use raw finally.

answered Apr 30 '19 at 18:23

Gabriel Tong

206
3
12

Ordering by multiple columns in Prisma

2 Answers2