Order on a foreign table about supabase-js HOT 6 OPEN

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 })

from supabase-js.

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

from supabase-js.

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?

from supabase-js.

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?

from supabase-js.

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.

from supabase-js.

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";
}

from supabase-js.