1

I have the following structure

create table organisation (
  id uuid primary key,
  organisation_name varchar not null
);

create table organisation_member (
  id uuid primary key,
  email varchar not null,
  name varchar not null,
  organisation uuid not null,
  uid uuid,
  foreign key (organisation) references organisation(id),
  foreign key (uid) references users(id)
);

create table organisation_member_invite (
  id uuid primary key,
  organisation_member uuid not null,
  uid uuid not null,
  foreign key (organisation_member) references organisation_member(id),
  foreign key (uid) references users(id)
)

Using Postgrest with supabase, I'd like to query all organisations that are linked to an organisation_member_invite. In SQL I would just make a simple left join

select *, o.* from organisation_member_invite as omi
right join organisation_member as om on om.id = omi.organisation_member
left join organisation as o on o.id = om.organisation
where omi.user = <user_id>

Using postgrest, this turns into something like

...from('organisation_member_invite').select('''
*,
organisation_member(
  organisation(*)
)
''').eq('user', uid);

Unfortunately, I get the following output when printing several parts of the error variable.

print(error.hint)

Try changing 'organisation' to one of the following: 'organisation!organisation_member_organisation_fkey', 'organisation!organisation_function', 'organisation!organisation_member_invitation'. Find the desired relationship in the 'details' key.

print(error.details)

details: [
    {relationship: organisation_member_organisation_fkey[organisation][id], embedding: organisation_member with organisation, cardinality: many-to-one}, 
    {relationship: public.organisation_function[organisation_function_member_fkey][organisation_function_organisation_fkey], embedding: organisation_member with organisation, cardinality: many-to-many}, 
    {relationship: public.organisation_member_invitation[organisation_member_invitation_membership_fkey][organisation_member_invitation_organisation_fkey], embedding: organisation_member with organisation, cardinality: many-to-many}
]

print(error.message)

Could not embed because more than one relationship was found for 'organisation_member' and 'organisation'

. I don't have any other relationship besides the foreign key from organisation in organisation_member. I really like this notation but it also limits me from expressing which data I really want.

What am I doing wrong?

Alb
  • 1,063
  • 9
  • 33
  • Please paste the full error, there's a `details` and `hint` keys in the error response. – Steve Chavez Feb 21 '22 at 16:48
  • @SteveChavez I've added the extra data in my post – Alb Feb 21 '22 at 18:54
  • 1
    As mentioned by the error message, postgrest also finds some many-to-many relationships. Try replacing `organisation()` with `organisation!organisation_member_invitation()` in your JS snippet. – Steve Chavez Feb 22 '22 at 02:41
  • @SteveChavez hmm, I could've sworn that I've tried that. Well, it does give me the organisation for each invite. However, every result gives me an array with a single entry of the organisation. This should not be an array and every `invitation` entry has a distinct `organisation` linked to it (through `member`) – Alb Feb 22 '22 at 12:10
  • Hm, that seems like a different issue. Btw, you can pretty much copy/paste your SQL query and put it in a SQL function and then call it through [rpc](https://supabase.com/docs/reference/javascript/rpc). – Steve Chavez Feb 22 '22 at 16:39

0 Answers0