Order parent and children?

[richcorbs]

In an order expression is there a way to order the parent objects and the nested/foreign/child objects? Like this:

supabase.from("parents").select("*, children(*)").order([["parents.name", true], ["children.name", true]])

[steve-chavez]

Hey @richcorbs.

Can you try doing it as:

supabase
.from("parents")
.select("*, children(*)")
.order('name', { ascending: false})
.order('name', { foreignTable: 'children', ascending: false})

(I'm getting the example from a postgrest-js test)

[kiwicopple]

If you're on the current version of supabase-js, it may be something like this:

supabase
  .from("parents")
  .select("*, children(*)")
  .order('name.desc')
  .order('children.name.desc')

[richcorbs]

Looks like maybe this version isn't available via unpkg?

[richcorbs]

children.name.desc isn't working in 1.0.3:

column parent.children.name.desc does not exist

Maybe a regression?

[soedirgo]

Can you try doing it like Steve's example above? We changed the order API during the 1.0 transition.

[richcorbs]

Steve's example worked! Thank you for reminding me to try it!

[richcorbs]

Something weird is going on:

This order: order('finishers.time_in_seconds.asc')...

Generates this query string: finishers.order: time_in_seconds.desc.nullslast...

But this order: order('finishers.time_in_seconds', {ascending: true}...

Generates this query string: finishers.order: time_in_seconds.asc.nullslast...

But I get back the following finishers with time_in_seconds:

412
416
418
420
518
409

The only difference is the 409 finisher has a registration_id value and the others do not which I don't understand why it makes a difference in the sorting.

[kiwicopple]

We are upgrading the libaries now and I suspect you're caught between our library upgrades. I get the feeling that the old supabase-js has a bug in the order() function that we just didn't know about. Could you do us a big a favour and see if you can use your top query using this supabase-js library:

npm install github:kiwicopple/supabase-js#feature/migrate_to_typescript

But be aware that there are some other breaking changes in the new library: https://www.notion.so/supabase/Supabase-improved-DX-46907b88d337471791bc9b8104ecba39.

In case you can't be bothered with that - just wait a couple of weeks and we will push the new library live to NPM which has all the fixes, (tracked here: supabase/supabase-js#50)

[richcorbs]

No matter how I try to sort finishers it sorts by inserted_at (or updated_at since they are the same).