Ability to sort results by multiple attributes

[albertoperdomo]

Problem

Most databases today support sorting query results by multiple fields, but Prisma Client only supports sorting results by a single field:

Suggested solution

Ability to provide a sorted set of fields to sort the results by and if the sorting by each field should be ascending or descending. The set of fields should be sorted since (most?) databases will do the sorting sequentially.

PostgreSQL:

When more than one expression is specified, the later values are used to sort rows that are equal according to the earlier values. Each expression can be followed by an optional ASC or DESC keyword to set the sort direction to ascending or descending. ASC order is the default. Ascending order puts smaller values first, where "smaller" is defined in terms of the < operator. Similarly, descending order is determined with the > operator. [1]

const posts = await prisma.post.findMany({
  where: {
    author: { email: 'bob@prisma.io' },
  },
 orderBy: [
   {title: asc},
   {createdAt: desc}
 ]
})

Alternatives

Not considered.

Additional context

• sort with orderBy lists #337
• Support case sensitivity when querying and sorting #690
• Database collation affects result of orderBy (case sensitive) #2369

[fenos]

I think this is one of the most important and critical features you ever want to see in Prisma as a developer.

With this feature alone, you'll give to developers like me a no turn back to other libraries such as knex / typeorm etc... Unfortunately, this is not the case today.

At some point in your application, you'll have to sort by multiple fields and you'll do that more often than not.

To work around it today this we need to either write raw SQL queries which are kind of fine, but you lose all the purpose of using Prisma in first place.

With this functionality into place, believe me, there is no reason to not choose Prisma for your next project.

[mavilein]

@dpetrick will do an investigation to see what needs to be done.

[softmarshmallow]

seconded with @fenos 's opinion. this is critical.

[timsuchanek]

This is now available in 2.4.0-dev.52

[uni-zheng]

Hi, @timsuchanek I saw the code for this feature. But I have a concern that whether you can you use the property definition order of an Object to determine the order of sorts?

If I haven't make mistake there is I found the code to covert Object args objectToArgs

And it's use Object.entries method.

There is a description I found in MDN. link

The order of the array returned by Object.entries() does not depend on how an object is defined.

Is that means even maybe most JS engine make Object definition order and iterator order same, but it's not 100% working right?

Maybe use Array instead of Object is better.

[timsuchanek]

Thanks for the feedback @uni-zheng!

Thanks to your feedback and feedback we got after the release, we're now reconsidering moving to an array syntax instead. JavaScript itself seems to not have a problem with this anymore, see https://www.stefanjudis.com/today-i-learned/property-order-is-predictable-in-javascript-objects-since-es2015/

However, a new information we didn't have while designing the API, is that there is an eslint plugin that sorts the keys: https://eslint.org/docs/rules/sort-keys

That plugin is basically a "killer argument". We'll discuss this internally and give an update here.

[timsuchanek]

We heard you - and just released a fix to go with the array syntax ;) https://github.com/prisma/prisma/releases/tag/2.4.1

[internalfx]

@timsuchanek Would prisma be willing to support object and array syntax at the same time, so the user can choose?

[janpio]

What would be the motivation behind that @internalfx? Supporting an API syntax that might be affected by side effects seems like a bad idea and source for hard to debug bugs.

[internalfx]

Assuming this is true, Isn't there only a problem if you use eslint sort-keys? (I'm making an assumption here that this is what you mean by "side-effects")

I only ask because it seems like it would be trivial (not having seen the code myself) to support both methods.

[Sytten]

It would most likely be trivial, but really confusing for new users. The newer syntax is not exactly great, but it does the job. This is why many proposed shortcuts syntax ( like{ where: 'myid' }) have not been implemented.

[fchu]

@timsuchanek FYI the documentation isn't updated yet (totally understandable), I used the object syntax, got an error and had to find this issue to understand how to resolve it

Also the error made it sound like the feature wasn't implemented: Argument orderBy of type XXX needs exactly one argument, but you provided AAA and BBB. Please choose one.

(Been using prisma for a few days, and it's been a delight)

[pantharshit00]

Thanks for reporting @fchu. Please follow this docs issue: https://github.com/prisma/docs/issues/760

Someone will pick this up.