Querying Joins and Nested tables issue

I'm having an many-to-one issue on a query that I can't solve. Giving:

create table profiles {
  "id" uuid primary key,
  -- ...
  "created_by" uuid references profiles, -- profiles_created_by_profiles_id_fk
  "updated_by" uuid references profiles -- profiles_updated_by_profiles_id_fk
};

create view profiles_with_crew_owner_home_places as (
  select p.id as profile_id
  -- ....
);

create table profiles {
  "id" uuid primary key,
  -- ...
  "created_by" uuid references profiles, -- profiles_created_by_profiles_id_fk
  "updated_by" uuid references profiles -- profiles_updated_by_profiles_id_fk
};

create view profiles_with_crew_owner_home_places as (
  select p.id as profile_id
  -- ....
);

I need to reproduce the query below using supabase client syntax:

select p.id, q.profile_id from profiles as p
join profiles_with_crew_owner_home_places as q on p.id = q.profile_id
where p.id = 'abc';

select p.id, q.profile_id from profiles as p
join profiles_with_crew_owner_home_places as q on p.id = q.profile_id
where p.id = 'abc';

When I try this:

const { data, error } = await supabase.from('profiles')
  .select(`
    id, profiles_with_crew_owner_home_places(profile_id)
  `)
  .eq('id', 'abc');

const { data, error } = await supabase.from('profiles')
  .select(`
    id, profiles_with_crew_owner_home_places(profile_id)
  `)
  .eq('id', 'abc');

I get this error: i've added the error in the thread due to character limit. Which is fine, but basically it is telling me to do something like this:

const { data, error } = await supabase.from('profiles')
  .select(`
    id, profiles_with_crew_owner_home_places!profiles_created_by_profiles_id_fk(profile_id)
  `)
  .eq('id', 'abc');

const { data, error } = await supabase.from('profiles')
  .select(`
    id, profiles_with_crew_owner_home_places!profiles_created_by_profiles_id_fk(profile_id)
  `)
  .eq('id', 'abc');

Which makes the query to work, but it's not what I want, because now it's relating profiles_with_crew_owner_home_places.profile_id to profiles.created_by which is wrong. I need to relate profiles_with_crew_owner_home_places.profile_id with profiles.id. I've tried it in several ways but nothing works. Please tell me that It has a solution.

7 Replies

cesarOP•3d ago

Query error:

{
  code: 'PGRST201',
  details: [
    {
      cardinality: 'many-to-one',
      embedding: 'profiles with profiles_with_crew_owner_home_places',
      relationship: 'profiles_created_by_profiles_id_fk using profiles(created_by) and profiles_with_crew_owner_home_places(profile_id)'
    },
    {
      cardinality: 'many-to-one',
      embedding: 'profiles with profiles_with_crew_owner_home_places',
      relationship: 'profiles_updated_by_profiles_id_fk using profiles(updated_by) and profiles_with_crew_owner_home_places(profile_id)'
    }
  ],
  hint: "Try changing 'profiles_with_crew_owner_home_places' to one of the following: 'profiles_with_crew_owner_home_places!profiles_created_by_profiles_id_fk', 'profiles_with_crew_owner_home_places!profiles_updated_by_profiles_id_fk'. Find the desired relationship in the 'details' key.",
  message: "Could not embed because more than one relationship was found for 'profiles' and 'profiles_with_crew_owner_home_places'"
}

{
  code: 'PGRST201',
  details: [
    {
      cardinality: 'many-to-one',
      embedding: 'profiles with profiles_with_crew_owner_home_places',
      relationship: 'profiles_created_by_profiles_id_fk using profiles(created_by) and profiles_with_crew_owner_home_places(profile_id)'
    },
    {
      cardinality: 'many-to-one',
      embedding: 'profiles with profiles_with_crew_owner_home_places',
      relationship: 'profiles_updated_by_profiles_id_fk using profiles(updated_by) and profiles_with_crew_owner_home_places(profile_id)'
    }
  ],
  hint: "Try changing 'profiles_with_crew_owner_home_places' to one of the following: 'profiles_with_crew_owner_home_places!profiles_created_by_profiles_id_fk', 'profiles_with_crew_owner_home_places!profiles_updated_by_profiles_id_fk'. Find the desired relationship in the 'details' key.",
  message: "Could not embed because more than one relationship was found for 'profiles' and 'profiles_with_crew_owner_home_places'"
}

garyaustin•3d ago

There is this tool: https://supabase.com/docs/guides/api/sql-to-rest Basically the error shows you your choices for how to specify the relationship you want from the ones that exist between tables. Note they could be thru other tables also.

cesarOP•2d ago

Thanks for the help, this tool is very helpful. Unfortunately the suggestion it gave throwed the same error.

const { data, error } = await supabase
  .from('profiles')
  .select(
    `
    id,
    ...profiles_with_crew_owner_home_places!inner(
      profile_id
    )
    `,
  )
  .eq(
    'id',
    'abc',
  )

const { data, error } = await supabase
  .from('profiles')
  .select(
    `
    id,
    ...profiles_with_crew_owner_home_places!inner(
      profile_id
    )
    `,
  )
  .eq(
    'id',
    'abc',
  )

garyaustin•2d ago

You need to use one of the hints in your error to tell it which relationship to use. There is more than one between those tables.

cesarOP•2d ago

the point is that none of the hints is what i want, i want to use profile's primary key as reference. Nevertheless i'm moving towards using raw sql for this. Gave up.

garyaustin•2d ago

I just realized you are trying to use a view in this. The API only relies on Foreign keys of the underlying tables. You can't do joins if there is not a table to table FK relation. But sometimes it is better just to use an rpc to a table returning function as the REST API will not cover all cases.

cesarOP•2d ago

I think that sucks because: 1. if profiles didn't have the created_by and updated_by nested relations, the join would probably work. 2. I can do it easily with RAW SQL so it's clearly a PostgREST limitation. Yeah, i'm going to do that. Thanks a lot man.

Gaming

Programming

Querying Joins and Nested tables issue

Did you find this page helpful?