Supabase•3mo ago

Searched for a foreign key relationship between 'autosend' and 'profile' in the schema 'autosend'

Hi! Having a supabase issue, I have two schemas. One called public and another called autosend I have a table in the autosend schema that references a FK profiles.id in the public schema, for some reason when trying to access it through supabase-js I get this error

 {
  code: 'PGRST200',
  details: "Searched for a foreign key relationship between 'autosend' and 'profile' in the schema 'autosend', but no matches were found.",
  hint: null,
  message: "Could not find a relationship between 'autosend' and 'profile' in the schema cache"
}

 {
  code: 'PGRST200',
  details: "Searched for a foreign key relationship between 'autosend' and 'profile' in the schema 'autosend', but no matches were found.",
  hint: null,
  message: "Could not find a relationship between 'autosend' and 'profile' in the schema cache"
}

How can I fix this?

8 Replies

staticOP•3mo ago

You can see that it's a FK to the public.profiles table

  const supabase = await createSupabaseServerClient<"autosend">({ schema: "autosend" });
  const query = supabase.from("autosend").select("status, user, profile(sport), template, preferences");
  const { data, error } = await query.eq("profile.sport", sport).maybeSingle();

  const supabase = await createSupabaseServerClient<"autosend">({ schema: "autosend" });
  const query = supabase.from("autosend").select("status, user, profile(sport), template, preferences");
  const { data, error } = await query.eq("profile.sport", sport).maybeSingle();

garyaustin•3mo ago

Use profiles in the query maybe.

staticOP•3mo ago

Yeah, that doesn't do it either:

staticOP•3mo ago

I'm curious if it's search_path is in the autosend schema instead of being directed to profiles

garyaustin•3mo ago

Missed that it is another schema You can’t do multiple schema operations in the Rest API You’ll need a view or RPC call

staticOP•3mo ago

dang that sucks Alright, thanks then @garyaustin out of curiosity.. hope you don't mind the ping but is this safe?

CREATE OR REPLACE VIEW autosend."profile" 
WITH (security_invoker = ON) AS
SELECT 
  autosend.status,
  autosend.user,
  autosend.profile,
  profiles.sport,
  template,
  preferences
FROM autosend.autosend
JOIN profiles ON profiles.id = autosend.profile
WHERE CURRENT_USER = 'postgres' OR CURRENT_USER = 'service_role' OR autosend.user = auth.uid();

CREATE OR REPLACE VIEW autosend."profile" 
WITH (security_invoker = ON) AS
SELECT 
  autosend.status,
  autosend.user,
  autosend.profile,
  profiles.sport,
  template,
  preferences
FROM autosend.autosend
JOIN profiles ON profiles.id = autosend.profile
WHERE CURRENT_USER = 'postgres' OR CURRENT_USER = 'service_role' OR autosend.user = auth.uid();

I noticed I wasn't able to see the view's data but wasn't sure if this was a smart/safe approach as this was my first time with a view

garyaustin•3mo ago

Using invoker means obey RLS on the table. Can’t look closer now.

staticOP•3mo ago

Alright cool

Gaming

Programming

Searched for a foreign key relationship between 'autosend' and 'profile' in the schema 'autosend'

Did you find this page helpful?