Ordering by foreign key count

[brycedorn]

Hi, is it possible to do something like this:

await supabase.from("groups").select('*, items(id)').order('items.count');

Basically, sort resulting groups by the number of items tied to that group (group_id === id). Ideally I'd like to avoid keeping a column to track items.count and instead let order handle it.

[steve-chavez]

Not possible directly for now but you can do so with a computed column.

create function items_count(groups) returns bigint as $$
  select count(*) from items where group_id = $1.id;
$$ stable language sql;

await supabase.from("groups").select('*, items(id)').order('items_count');

[brycedorn]

Thank you @steve-chavez! I wasn't sure how to structure/call the function, this works perfectly.

[devalensio]

@steve-chavez where do we write the items_count (computed column) in our supabase dashboard ?

[steve-chavez]

@devalensio Since it's just a SQL function, you can use the UI SQL editor.

I think the UI Function Editor will not work now because it doesn't detect table type arguments(you could request this is as a feature).

[devalensio]

@steve-chavez thanks 🙏🏼

[hasanneke]

@steve-chavez Amazing, thanks!