'Nested query in postgrest (supabase) denies existence of relationship between two tables

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source