Ordering source table using foreign table

[andresiggia]

Bug report

Describe the bug

Ordering selected rows using foreignTable option does not work

To Reproduce

(table1 has fields table1_id and table2_id) (table2 has fields id, title and others)

const { data, error } = await this.supabase
    .from('table1')
    .select('table2(*)')
    .eq('table1_id', table1_id)
    .order('title', { foreignTable: 'table2', ascending: true });

Expected behavior

Changing the ascending option from true to false should invert the order of the results, or if there's any issue with the query I'd expect to get an error. None of those happen, the query returns the same results in the same order, with or without the order selection.

System information

• OS: macOS
• Browser (if applies) chrome
• Version of supabase-js: 1.20.0
• Version of Node.js: 12.9.0

[steve-chavez]

Hey @andresiggia,

hanging the ascending option from true to false should invert the order of the results,

Do you expect to order table1 rows by using a filter on table2? Right now ordering on the foreign table only affects its rows - not the source table rows(table1 rows in this case).

Edit: Related:

• https://github.com/PostgREST/postgrest/issues/1414
• https://github.com/supabase/supabase/discussions/1169
• https://github.com/supabase/supabase/discussions/4549#discussioncomment-2827694

[andresiggia]

Hi @steve-chavez thanks for your reply. Would there be a way then to order table1 results by a field in table2?

[oskery]

I'm looking for something similar. Someone with more experience than me that have a suggested workaround in the meantime?

In my db I have items which can appear in multiple stores. Items have a relationship to store_items which in turn holds a price. I would like to order items based on their cheapest price in any store.

Ordering client-side is not an option since the database is large and I am limiting the result.

let { data: items, error } = await supabase
  .from("items")
  .select("id, name, store_items (price)")
  .order("price", { foreignTable: "store_items", ascending: true })
  .limit(20)

[steve-chavez]

@oskery For now, a workaround would be doing the SQL directly on a function and calling it through rpc.

[NicolaSpadari]

Bump, still an issue

[arclogos132]

So sad still can't use this.

[KiyeopYang]

I need this feature so much

[arclogos132]

It's been a year since OP has brought up this issue... can we get an update on this feature?

[ComputelessComputer]

Well...dang. Still no news? Might as well just do this on the client side.

[steve-chavez]

FYI, this one is more or less done on https://github.com/PostgREST/postgrest/pull/2511.

For now, the interface will be like

const { data, error } = await this.supabase
    .from('clients')
    .select('clients_details(id,address,paycheck)')
    .order('clients_details(paycheck)', { ascending: true })

---

The infra team will discuss self-serve upgrades this week. This would enable users to upgrade their postgREST version manually and try pre-releases' features.

[juanvilladev]

Hey @steve-chavez , thanks for the awesome update. Once Supabase users can upgrade their version of Postgrest, will that be enough to start using the supabase client to order by a foreign column?

[steve-chavez]

@juanvilladev Sorry for the late reply here.

Ordering by related tables is available on the Supabase CLI.

[barbinbrad]

@steve-chavez thank you so much!

[Kamahl19]

@steve-chavez I am probably missing something but ordering by joined table still doesnt work for me.

await supabase
        .from('reading_list')
        .select(
          `
          *,
          books(*)
        `
        )
        .eq('userId', user.id)
        .order('title', { foreignTable: 'books', ascending: false });

Am I doing something wrong? Thanks!

[arnoson]

@steve-chavez , I'm trying your example

const { data, error } = await this.supabase
    .from('clients')
    .select('clients_details(id,address,paycheck)')
    .order('clients_details(paycheck)', { ascending: true })

and getting the following error unexpected '(' expecting letter, digit, \"-\", \"->>\", \"->\", delimiter (.), \",\" or end of input

[arnoson]

Btw, I'm using supabase cloud, as I assumed that by now this would be integrated. Or is this still a fetaure that only works in supabase CLI?

[Kamahl19]

I am using CLI for local development only and nothing mentioned in this blog post works for me https://supabase.com/blog/postgrest-11-prerelease . Am I missing something?

[steve-chavez]

Sorry, v11 is still not available on cloud or CLI (https://github.com/supabase/cli/pull/1218).

Please subscribe here https://github.com/supabase/postgres/pull/628#issuecomment-1544426059, will notify once v11 is deployed.

[turch]

@steve-chavez

I've upgraded to v11 and can now use your sample syntax successfully:

supabase
    .from('clients')
    .select('clients_details(id,address,paycheck)')
    .order('clients_details(paycheck)', { ascending: true })

However, is it possible to have this go more than one level? e.g. if clients_details has another FK to client_type, can I do something like this:

supabase
    .from('clients')
    .select('clients_details(id,address,paycheck)')
    .order('clients_details(client_type(name))', { ascending: true })

[steve-chavez]

@turch Not possible yet. Can you open an issue?

https://github.com/PostgREST/postgrest/issues/

https://postgrest.org/en/latest/references/api/resource_embedding.html#top-level-ordering

[jonathanlal]

FYI, this one is more or less done on PostgREST/postgrest#2511.

For now, the interface will be like

const { data, error } = await this.supabase
    .from('clients')
    .select('clients_details(id,address,paycheck)')
    .order('clients_details(paycheck)', { ascending: true })

The infra team will discuss self-serve upgrades this week. This would enable users to upgrade their postgREST version manually and try pre-releases' features.

this worked - thanks!

[NelsonRay]

FYI, this one is more or less done on PostgREST/postgrest#2511.

For now, the interface will be like

const { data, error } = await this.supabase
    .from('clients')
    .select('clients_details(id,address,paycheck)')
    .order('clients_details(paycheck)', { ascending: true })

The infra team will discuss self-serve upgrades this week. This would enable users to upgrade their postgREST version manually and try pre-releases' features.

This worked! So weird that OP's approach still doesn't work.

[Mihai-github]

Is it possible to sort by accessing the value of the first join?

Example

simpler example would be you order records from table A by accessing a column in table B but what I need is starting from records in table A I wanna order them by making a join from table B on table C and here i wanna order the records providing one column in C to be used

is it possible?