Order on a foreign table

[bllakcn]

Bug report

• [x] I confirm this is a bug with Supabase, not with my own application.
• [x] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

Hey guys, I am not an expert around here but hear me out :)

Based on docs here on how to order the query result on a referenced table .

const { data, error } = await supabase
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)
  .order('name', { referencedTable: 'cities', ascending: false })

First issue is that, maybe the docs is outdated but, this options key is called foreignTable in the supabase-js package 2.38.3.

Secondly, when i add foreignTable option to order the foreign table that comes with the query, in Typescript, I am given the columns of the base table of the query (countries in this example) as autocomplete. Though, I can still use the desired column name as the order value and TS will not yell about it.

Expected behavior

If I want to order the foreign table and add that option key, TS should recognize that so I should be able to use the columns of that table to order. The inferred type is still the columns of the base table.

System information

• Version of supabase-js: [2.38.3]

[KajSzy]

You can workaround for this issue using foreign table reference same as in select method

const { data, error } = await supabase
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)
  .order('cities(name)', { ascending: false })

[cervantes-x]

You can workaround for this issue using foreign table reference same as in select method

const { data, error } = await supabase
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)
  .order('cities(name)', { ascending: false })

When I try this I get the following Postgres error:

{
  code: 'PGRST100',
  details: `unexpected '(' expecting letter, digit, "-", "->>", "->", delimiter (.), "," or end of input`,
  hint: null,
  message: '"failed to parse order (product_category(name).desc)" (line 1, column 17)'
}

This is my specific query:

const {data, error} = await supabase.from("product").select("id, product_category(name)").order("product_category(name)", { ascending: false })

Any idea how to work around this?

[KajSzy]

This requires PostgREST v11. To force update remote supabase you need to pause it and restore it. To update local supabase, update supabase-cli and restart whole supabase using stop and start

[cervantes-x]

I had to change the structure of my relations a bit, so now I have a many-to-many relationship between my products and my product categories. I created a separate table fot this called product_to_product_category.

I was hoping that I could still order by the name value but unfortunately it doesn't work with the following call:

const {data, error} = await supabase
.from("product")
.select("id, product_to_product_category(product_category(name, primary))")
.order("name", { ascending: false, referencedTable: product_to_product_category.product_category })

Is there any way to make this work?

[StephenTangCook]

Hi, I have been unable to get order with a referencedTable to affect the results of the parent table. In other words, these docs have been untrue for me. Others have hit this too.

FWIW @KajSzy 's workaround is working for me.

I wanted to get clarity on this open issue. Is it related to the functionality of ordering with referencedTable, or Typescript typing errors? If it's just typing, should I open a separate issue? Thanks.

[Ruchita1010]

Facing the same issue. Using referenceTable isn't working.

const {data, error} = await supabase
  .from('haikus')
  .select(
    'id, content, created_at, profiles(id, username, avatar_path), likes!inner()'
  )
  .eq('likes.profile_id', userId)
  .order('created_at', { referencedTable: 'likes', ascending: false });

You can workaround for this issue using foreign table reference same as in select method

const { data, error } = await supabase
  .from('countries')
  .select(`
    name,
    cities (
      name
    )
  `)
  .order('cities(name)', { ascending: false })

Tried doing this, but I got an error:

const {data, error} = await supabase
  .from('haikus')
  .select(
    'id, content, created_at, profiles(id, username, avatar_path), likes!inner()'
  )
  .eq('likes.profile_id', userId)
  .order('likes(created_at)', { ascending: false });

{
  code: 'PGRST118';
  details: "'haikus' and 'likes' do not form a many-to-one or one-to-one relationship";
  hint: null;
  message: "A related order on 'likes' is not possible";
}