Allow sorting by NULLS FIRST / NULLS LAST

[cheapsteak]

Problem

Currently can't seem to specify how to sort nulls in a findMany

Suggested solution

To be able to specify NULLS FIRST or NULLS LAST

Alternatives

Currently making separate queries and concat-ing them in JS

Additional context

On a table with rows that contain data synced from elsewhere, there's a date field for last_synced_details_at;
The column's default value is null (never been synced)
When attempting to get the "most stale rows" would be orderBy: { last_synced_details_at: "asc" }, I'd get the ones that have been synced first, and rows that have never been synced after

[GainorB]

Need this. Any updates on this functionality?

[rdgb]

I would like this feature too.

[sbkrightly]

Need this, would prevent having to convert a large prisma query to a raw query every time I want to add a sort with nulls last/first

[webgev]

Need this...

[Joelgullander]

Need this aswell... :)

[aledalgrande]

Is this a "good first issue"?

[cmcaboy]

We had to eject out and use raw sql for this 😿

[BlakeBrown]

+1 also looking for this

[dariusj18]

I think it would be best to look toward Hasura for the solution to this, add a few more values to the SortOrder enum: asc_nulls_first, asc_nulls_last, desc_nulls_first, and desc_nulls_last. This can easily map to generating the relevant SQL statement.

[jycouet]

Any idea of when this will be possible?
Should we PR the repo?

[steebchen]

I agree with @dariusj18 – this would then also automatically propagate to the Go client.

[meotimdihia]

why did this problem take 2 years already? I think it is like hardcoded or something dariusj18 said O_O.

[jycouet]

To not break existing, we could just add the 2 missing value in the sorting enum?
Should we PR the repo? :)

[dariusj18]

My guess is that the only difficulty would lie in the database support, so postgresql is the only one that would be simple out of the box. Most other databases require adding an additional CASE statement to the ORDER BY to force null sort weight, and mongodb requires other hacks. Which is why it would be nice to have that abstracted away in this library.

[jycouet]

You are right. ✅
As I'm using provider = "postgresql" I think mostly about my case.

But, maybe it could be implemented in postgresql fast and leave complicated stuff for later? :)

[janpio]

Your analysis is fully on point. As Prisma supports multiple databases, we can not easily just add the "simple" one and ignore the other ones. When a feature exists, it is hard to take it back - so we need to understand what it means (e.g. "not possible" or "super hard to build and maintain") for all the other databases.

You can help us by understanding the complexities this would create for all our other databases, and leave example and thoughts here as a comment. If we have most of the "logic" already figured out for a feature, it is of course much easier to tackle it.

[jycouet]

All right, let's try 💪

Let's consider tabletosort:

id	name
1	John
2	Bob
3	NULL
4	Zaz

PostgreSQL

ASC

SELECT * FROM tabletosort order by name -- default to NULLS LAST

-- How to put null first?
-- Option Native NULLS FIRST
SELECT * FROM tabletosort order by name NULLS FIRST
-- Option col IS NULL then
SELECT * FROM tabletosort order by name IS NULL desc, name

DESC

SELECT * FROM tabletosort order by name desc -- default to NULLS FIRST

-- How to put null last?
-- Option Native NULLS LAST
SELECT * FROM tabletosort order by name desc NULLS LAST
-- Option col IS NULL then
SELECT * FROM tabletosort order by name IS NULL, name desc

MySQL

ASC

SELECT * FROM tabletosort order by name -- default to NULLS FIRST

-- Option col IS NULL then
-- it's supported

DESC

SELECT * FROM tabletosort order by name desc -- default to NULLS LAST

-- Option col IS NULL then
-- it's supported

SQLite

ASC

SELECT * FROM tabletosort order by name -- default to NULLS FIRST

-- Option col IS NULL then
-- it's supported

DESC

SELECT * FROM tabletosort order by name desc -- default to NULLS LAST

-- Option col IS NULL then
-- it's supported

Microsoft SQL Server

ASC

SELECT * FROM tabletosort order by name -- default to NULLS FIRST

-- Option col IS NULL then
-- it's supported

DESC

SELECT * FROM tabletosort order by name desc -- default to NULLS LAST

-- Option col IS NULL then
-- it's supported

Summary

	PostgreSQL	MySQL	SQL Lite	Microsoft SQL Server
default asc	NULLS LAST	NULLS FIRST	NULLS FIRST	NULLS FIRST
default desc	NULLS FIRST	NULLS LAST	NULLS LAST	NULLS LAST
Support NULLS XXXX	✅	❌	❌	❌
col IS NULL then	✅	✅	✅	✅

Proposal

type orderBy = {
  direction:
  | 'asc' // platform asc
  | 'desc' // platform desc
  | 'asc_nulls_first' // force null first then asc
  | 'asc_nulls_last' // force null last then asc
  | 'desc_nulls_first' // force null first then desc
  | 'desc_nulls_last'; // force null last then desc
};

...

await this.prisma.tabletosort.findMany({
  orderBy: { nom: 'asc' }
});

Like this, there is NO change of the current behavior and you can opt-in a specific sorting.

🤞 that the topic can move forward 🥳
//BTW, I 🧡 prisma.

[n1ghtmare]

@jycouet's proposal looks amazing, please look into this, this is a very common case!

[dariusj18]

For MS SQL Server you can use ORDER BY CASE WHEN column_name IS NULL THEN 1 ELSE 0 END for NULLS LAST

[jycouet]

To bad that process.candidate was removed!
But I still have hopes 🙏

---

In the meantime, I have a workaround to trick the system that I want to share.

1. Trick the model

model my_table {
  myDate           DateTime?    @db.Date          //   <== the date that can be NULL we want to sort.
  
  // We have to leave the field nullable (even if will never be the case) 
  // If not prisma want us to pass value on create!
  myDateGenerated  DateTime?    @db.Date          //   <== the generated field
}

2. Create a migration with --create-only
3. Tweak the migration to put the formula you want. In my case, 1999-12-31 will be way before any dates.

ALTER TABLE "my_table" ADD "myDateGenerated" DATE GENERATED ALWAYS AS (CASE WHEN "myDate" IS NULL THEN '1999-12-31' ELSE "myDate" END) STORED

4. Enjoy 🥳
   You can now orderBy: { myDateGenerated: 'asc' }
   You can of course display myDate and have NULL first with this workaround!

---

This trick is a also a nice candidate for @readonly => #3401

[casey-chow]

This makes a lot of sense as a workaround! Just wanted to drop a note that may be useful:

In my case, 1999-12-31 will be way before any dates.

Postgres does have support for infinite timestamps, so it may be a little more strictly correct to use them if there's worries about representing really old dates correctly.

https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES

[Weakky]

Hey folks,

This feature was released as part of the 4.1.0 version.

Thanks for upvoting this feature. If you wanna give any feedback, please comment on this issue #14377

Cheers 👋

[jycouet]

Great news!!!
I'll test it sooooon 🎉