RFC: order() with foreign tables

[kiwicopple]

Currently the order() function works like this:

const { data: products, error } = await supabase
        .from('films')
        .select('*, actors(*)')
        .gt('actors.gender', 'female')
        .order('first_name', { foreignTable: 'actors' })

// Creates:
// GET /films?select=*,actors(*)&actors.gender=eq.female&actors.order=first_name

There is a difference between the filtering on embedded tables (.gt('prices.unit_amount', 0)) and ordering of foreign tables (.order('unit_amount', { foreignTable: 'prices' }))

We have an option to convert order() to be more inline with filters:

const { data: products, error } = await supabase
        .from('films')
        .select('*, actors(*)')
        .order('actors.last_name')
        .order('actors.first_name')

// Creates: 
// GET /films?select=*,actors(*)&actors.order=last_name,first_name

To do this we will have to split('.') on fullstops, and aggregate all of the order() functions together.

Pending questions:

• Is this better DX?
• Are their fringe cases where this doesn't work?
• Does this work for ordering foreign tables at depth >= 3?

[embiem]

I'm currently interested in this. As I understand, this would allow me to order by a value of the foreign table. Similar to a query like so, correct?

select
  *
from
  "Table1"
inner join "Table2"
  on "table_1_hash" = "table_2_hash"
order by
  "table_2_number" desc
limit
  30;

What I want to achieve is ordering by a value that is part of the foreign table.

I tried it like so, but it didn't work (wasn't ordering by the table_2_number)

  const { data, error, count } = await supabase
    .from("Table1")
    .select(`*,Table2(*)`, { count: "exact" })
    .order("table_2_number", { ascending: false, foreignTable: "Table2" })
    .range(page * PER_PAGE, page * PER_PAGE + (PER_PAGE - 1))

[soedirgo]

I don't think this is possible even on the PostgREST side (ref), you can do filters/order/etc. on foreign tables but they don't filter/order the first table themselves.

@kiwicopple somehow I missed this - I guess we can put this in supabase/supabase-js#170?