Supabase query for many to many relationship

Question

I am trying to find a proper way to query m2m data using supabase client.

I have three tables:

public.sites

column	format
id	int8
name	text

auth.users (supabase auth table)

column	format
..	...
id	uuid

public.members

left	center
user_id	uuid foreign key to auth.users.id
site_id	int8 foreign key to public.sites.id

I need to query all the sites given the userid. I made several attempts without any success such as:

const sites = await supabase.from('sites').select('id, name, members(user_id)').eq('members.user_id', user.id);

P.s. i also ran

alter table members
add constraint pk_user_team primary key (user_id, site_id);

based on few posts here on stacksoverflow.

any advice?

Answer 1

I found the solution myself. I need to use an inner join on the table and then filter on user_id:

const sites = await supabase.from('sites').select('*, members!inner(user_id)').eq('members.user_id', user.id)

Probably there is a better or more efficient way. Any advice is still welcome!