I'm trying to get the mentioned many-to-many join feature to work as described here: https://supabase.com/docs/guides/api/joins-and-nesting#many-to-many-joins
I have three simple tables:
create table parties (id uuid primary key,name text not null);create table users (id uuid not null references auth.users on delete cascade,phone_number text,primary key (id));create table parties_members (party_id uuid not null references parties on delete cascade,user_id uuid not null references users on delete cascade);alter table parties_members add primary key (party_id, user_id);I'm now trying create a query that joins parties with users but I get a: Could not find a relationship between 'parties' and 'users' in the schema cache, code: PGRST200
try {var parties = await supabase.from("parties").select('''id,parties_members(*)''');var users = await supabase.from("users").select('''id,parties_members(*)''');log("parties with join table: $parties");log("users with join table: $users");var partiesWithUsers = await supabase.from("parties").select('''id,users(*)''');} catch (e, stacktrace) {log("Error fetching parties", error: e, stackTrace: stacktrace);}The first two joins work... so it seems the references from either side are discovered, it just can't make the jump using the join table.
[log] parties with join table: [{id: b6e10467-7d1f-4705-8bb5-b59484c86955, parties_members: [{party_id: b6e10467-7d1f-4705-8bb5-b59484c86955, user_id: 48d4a132-46f1-439a-94b3-3ecb49d98a49}]}][log] users with join table: [{id: 48d4a132-46f1-439a-94b3-3ecb49d98a49, parties_members: {party_id: b6e10467-7d1f-4705-8bb5-b59484c86955, user_id: 48d4a132-46f1-439a-94b3-3ecb49d98a49}}]Any idea what I'm doing wrong?
Using: supabase_flutter: ^1.8.1
Best Answer
You were missing the primary key constraint on your parties_members table. You can create a composite primary key like this to complete the definition of a many to many relationship.
create table parties_members (party_id uuid not null references parties on delete cascade,user_id uuid not null references users on delete cascade,primary key(party_id, user_id));