0

I'm trying to figure out how to sort query results using supabase to do something like this:

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

Basically, sort resulting groups by the number of associated items (group has many items) descending, and return those items alongside group information.

Any pointers for how to structure the select to do this? I'm having trouble as docs aren't that helpful, so it's likely something I'd need raw sql for.

bryce
  • 3,022
  • 1
  • 15
  • 19

1 Answers1

0

You would need to create a view or database function to achieve this.

Here is an example sql to create a view.

create or replace view groups_with_items as
  select 
    -- ... any columns you would like to include in the view
    (select count(*) from public.items where public.groups.id = public.items.room_id) as count
  from public.groups
  left join public.items on public.groups.id = public.items.group_id;

Once you create this view, you can query this just like a regular table like this:

await supabase.from("groups_with_items").select('*').order('count');
dshukertjr
  • 15,244
  • 11
  • 57
  • 94