Sorting by multiple columns

[kaboc]

Feature request

Is your feature request related to a problem? Please describe.

post_id	title	created_at	updated_at
1	Post 1	2021-06-21 10:00:00.000000	\<null>
2	Post 2	2021-06-22 10:00:00.000000	2021-06-25 09:00:00.000000
3	Post 3	2021-06-23 10:00:00.000000	2021-06-24 17:00:00.000000
4	Post 4	2021-06-24 10:00:00.000000	2021-06-25 08:00:00.000000
5	Post 5	2021-06-25 10:00:00.000000	\<null>

I'd like to sort the records in the above table by both updated_at and created_at. Below is the expected result.

post_id	title	created_at	updated_at
2	Post 2	2021-06-22 10:00:00.000000	2021-06-25 09:00:00.000000
4	Post 4	2021-06-24 10:00:00.000000	2021-06-25 08:00:00.000000
3	Post 3	2021-06-23 10:00:00.000000	2021-06-24 17:00:00.000000
5	Post 5	2021-06-25 10:00:00.000000	\<null>
1	Post 1	2021-06-21 10:00:00.000000	\<null>

The records are sorted by updated_at in descending order with nulls at the end, and records with nulls in updated_at are sorted by created_at in descending order.

With SQL (the dialect of postgres), I can achieve this by writing the ORDER BY clause like below:

ORDER BY updated_at DESC NULLS LAST, created_at DESC

It doesn't seem the package has support for this, but having looked at the source code, I found that it was not impossible although it wasn't very easy.

order('created_at')

This is converted to:

ORDER BY ("created_at".desc.nullslast)

So what I'd like to do is possible if I write the sorting conditions as follows:

order('updated_at".desc.nullslast, "created_at', ascending: false)

This is too hacky to use with peace of mind. We need a better way.

Describe the solution you'd like

The solution may be to allow multiple settings to be passed to order() or to add a new method specialised for it.

For setting multiple options, some methods of this package use a string parameter, like or('status.eq.OFFLINE,username.eq.supabot'), but it's not very elegant nor safe.

It might be better to have a class like Order and pass the list of its objects.

e.g.

class Order {
  const Order(
    this.column, {
    this.ascending = false,
    this.nullsFirst = false,
    this.foreignTable,
  });

  final String column;
  final bool ascending;
  final bool nullsFirst;
  final String? foreignTable;
}

...

await client
    .from('posts')
    .select('*')
    // I don't come up with a nice method name...
    .order2([Order('updated_at'), Order('created_at')])
    .execute();

Describe alternatives you've considered

I also tried using multiple order()s like order('updated_at).order('created_at') but with no success. Should it actually work for my usage? It didn't give any error.

If using it in such a way has no effects currently, it may be better to make it work for this in that it is more intuitive for users.

I'm not familiar with this sort of query builders because I usually write raw SQL queries in backend servers, so other people with more knowledge are likely to have better ideas.

Additional context

I wondered if I should file this issue here, or in either repository of supabase/supabase or supabase/postgrest. Does a change in the API for a specific language require that it is applied to the APIs for other languages?

[dshukertjr]

Hi @kaboc !

Thanks for opening this issue! This feature has recently been added to postgrest-js on this PR.

If you have the time, it would be great if you could add this feature, but no pressure though. If you don't have the band width, I can work on it over the weekend!

[kaboc]

Hi @dshukertjr . I'll give it a try anyway and let you know if I have some difficulty.

[dshukertjr]

@kaboc Amazing!