PostgreSQL query to Supabase Query

Question

How can i make once of this PostgreSQL querys in supabase? I tried reading supabase doc but it doesn't work

select rooms.id as room_id, members.id as member_id, user_id from rooms 
inner join members 
on rooms.id = members.room_id 
where rooms.id = members.room_id

and user_id = 1

Or

select room_id, user_id, members.id from members 
inner join rooms 
on rooms.id = members.room_id 
where members.user_id = 1

Answer 1

No, there's not really any performance difference from calling an .rpc() vs. just calling the api directly. You should be fine. Just make sure to set up your database correctly (indexes, joins, etc.) for best performance.

Answer 2

You can query a foreign table using the Supabase client, but only if there's a foreign key set up:

Supabase select(): Query foreign tables

As stated in the comments, you can create a view for this purpose:

Create View

Or you can create a PosgreSQL function and call that with Supabase .rpc():