Join through another table
I have following tables:
and I want to join them through auth.users table on receiver_id and sender_id which are user id's, something like this but it doesn't work, can't find connection between profile and conversation tables:
-- conversations
create table if not exists public.conversation (
id uuid not null primary key DEFAULT uuid_generate_v4(),
sender_id uuid not null references auth.users (id) on delete cascade,
receiver_id uuid not null references auth.users (id) on delete cascade,
item_id uuid not null references public.item on delete cascade,
created_at timestamp default timezone('utc', now()),
unique(sender_id,receiver_id,item_id)
); -- conversations
create table if not exists public.conversation (
id uuid not null primary key DEFAULT uuid_generate_v4(),
sender_id uuid not null references auth.users (id) on delete cascade,
receiver_id uuid not null references auth.users (id) on delete cascade,
item_id uuid not null references public.item on delete cascade,
created_at timestamp default timezone('utc', now()),
unique(sender_id,receiver_id,item_id)
); -- profiles
create table if not exists public.profile (
id uuid not null primary key DEFAULT uuid_generate_v4(),
user_id uuid not null references auth.users (id) on delete cascade,
image_url text default null,
username text not null,
currency_id bigint references public.currency not null,
country_id bigint references public.country not null
); -- profiles
create table if not exists public.profile (
id uuid not null primary key DEFAULT uuid_generate_v4(),
user_id uuid not null references auth.users (id) on delete cascade,
image_url text default null,
username text not null,
currency_id bigint references public.currency not null,
country_id bigint references public.country not null
);and I want to join them through auth.users table on receiver_id and sender_id which are user id's, something like this but it doesn't work, can't find connection between profile and conversation tables:
return _client
.from(_conversationTable) //
.select('''
id,
sender:profile(*)&profile.user_id=sender_id,
receiver:profile(*)&profile.user_id=receiver_id,
item:item_id(*),
created_at
''')
.eq('id', conversationId)
.limit(1)
.single()
.execute() return _client
.from(_conversationTable) //
.select('''
id,
sender:profile(*)&profile.user_id=sender_id,
receiver:profile(*)&profile.user_id=receiver_id,
item:item_id(*),
created_at
''')
.eq('id', conversationId)
.limit(1)
.single()
.execute()