I’m writing this small tip of the day to describe the behavior when you order a query result and null values exist on a target column.
The Use Case
Let’s say we have a table with a created_at
column and another named in_progress_at
.
The first column never equals to null
while the second can until the record is updated.
Now, the business logic want to order records in descending order with the updated records first and then the rest.
If you order in descending order on the in_progress_at
first and then on created_at
also in descending order, what will happen if you have a record without a in_progress_at
set and another with no value on in_progress_at
?
Well, the record with the null value on in_progress_at
will come before first.
And what if you add a new record and update an existing one? Well, the new record will stay on top…
What about ascending order? It’s the opposite: the record with a null value comes second.
Testing With Supabase Dashboard
It’s easy:
- Create a Supabase account and a project.
- Create a table
entities
with the two date columns described above. - Insert a few records
- Use the SQL editor to test it:
|
|
The query above will give the following result:
|
|
The query above will give the following result:
Neither achieve the result expected. The entity 12 should arrive second and the entity 5 first.
Solution With SQL
The SQL solution is simple:
|
|
We get the right result:
Issue Using Supabase Public API
But how do you that using Supabase JavaScript client?
Not like that!
|
|
For the above code, you get an error :
|
|
How we solve that?
Solution Using Supabase Public API
The answer: Postgres functions!
In our case, it’ll look as follows:
|
|
To use it, run the SQL above, generate your TypeScript types using the CLI and update the TypScript code:
|
|
I could improve it, but it solves my business logic.
You can read more in the official Supabase documentation about:
Conclusion
That was a tricky one, as I didn’t know about Postgres functions. I had written a draft of this article and while completing it, I realized that I was far from the goal!
Did you learn something today? I sure did!
Follow me
Thanks for reading this article. Make sure to follow me on X, subscribe to my Substack publication and bookmark my blog to read more in the future.
Photo by Pavel Danilyuk.