Preview feature feedback: Order By Nulls (orderByNulls)

[Weakky]

Please share your feedback about the orderByNulls functionality released in v4.1.0 in this issue.

• If you encounter a bug, please open a bug report in this repo.
• If the feature is working well for you, please share this in a comment below or leave a 👍 on this issue.

If you have any questions, don't hesitate to ask them in the #prisma-client channel in the Prisma Slack.

[jashenco]

Hi there, I really like the orderByNulls functionality. Yet, there is one thing I can't get working. I'd like to display nulls as last, but without having to sort the non-null values. I am trying to sort users based on information being present, but I am using other filters aswell. e.g.: sorting users on ID desc and displaying user without a profile picture last. Any idea on how to use this feature to get to the desired result?

[ahammouda]

I like this feature, but I'd like to request the behavior of generated types changes a little if possible. If I have a model such as

model Table {
   value: Decimal @db.Money
   otherValue: Decimal?
}

And I set the order by in a findMany for example as

...
prisma.table.findMany({
   orderBy: {
      value: { sort 'desc', nulls: 'last' }
   }
})

This currently results in an error whereas if I sort by otherValue it does not. The reason being that the generated type for the find many args of value is simply Prisma.SortOrder whereas for otherValue it is Prisma.SortOrderInput | Prisma.SortOrder. This is frustrating in my particular case because I'm using postgres, and I can simply pass the nulls argument directly to a non-nullable field, and things just work (I realize there's a no-op here):

select *
from Table
order by value asc
null first

It's nice to be able to pass this as a no op for the purposes of developing an api against a dataset using this feature. I want that api to have null order parameters where I can use this feature, but I now have to ensure that I'm only using this feature for specific fields and not others which results in a far greater code footprint, and of course as the schema evolves it will require changes to this larger code footprint, making my use of this feature kind of brittle.

tldr; can you have prisma generate Prisma.SortOrderInput | Prisma.SortOrder types for sortable field arguments regardless of whether the field is nullable or not?

Just some food for thought - not sure if it's possible or an easy change given you need to accommodate more rdbms than just postgres.

[jack-linden]

Is there any intentions for supporting orderByNulls functionality for relations?

[ahammouda]

Is there any intentions for supporting orderByNulls functionality for relations?

Yes - doing an outer join and ordering by a related field which may be null in the joined table (even if not an optional field on the originating table), would be another great addition to this feature.

[Weakky]

Hey folks,

I'd like to display nulls as last, but without having to sort the non-null values.

@jashenco: It is currently not possible to express that via the API. That would look like orderBy: { field: { nulls: 'first' } }. Here's an existing feature request you can track for that #18022

tldr; can you have prisma generate Prisma.SortOrderInput | Prisma.SortOrder types for sortable field arguments regardless of whether the field is nullable or not?

@ahammouda Thanks for the suggestion. It's not entirely clear whether that wouldn't bring confusion for the overall API. Don't get me wrong, I understand the benefits of being able to blindly pass nulls: 'first' | 'last' regardless of the field you're ordering when using the client as an opaque DB client. I've just persisted your comment in a separate feature request #19760 so that it gets more visibility.

Is there any intentions for supporting orderByNulls functionality for relations?

@jack-linden There is but it was intentionally cut out of scope for now to only deal with the low-hanging fruit. I have persisted your feature request here #19761

[janpio]

Thanks everyone, orderByNulls is Generally Available since 4.16.0 🥳
If you have further feedback, you can open a new issue now. Thanks again!