I created a profiles table, where creator_id has a self-referencing one-to-one relationship. Since the referenced id column is unique, I expect the returned row to be an object.

create table public.profiles (
  id uuid not null,
  creator_id uuid not null default auth.uid (),
  first_name text not null,
  last_name text not null,
  constraint profiles_pkey primary key (id),
  constraint profiles_slug_key unique (slug),
  constraint profiles_id_key unique (id),
  constraint profiles_id_fkey foreign KEY (id) references auth.users (id) on delete CASCADE,
  constraint profiles_creator_id_fkey foreign KEY (creator_id) references profiles (id)
) TABLESPACE pg_default;

const { data, error } = await supabase.from('profiles').select(
  '*, creator:profiles!creator_id(id, first_name, last_name)'
)

Instead, the creator is returned as an array of objects:

{
  "id": "c2b63d53-30a9-4bca-9a41-d769e8d0a8ba",
  "first_name": "Hannah",
  "last_name": "Montana",
  "creator_id": "cd6e8ef1-2517-4f55-bfea-ba2dd81e4c55",
  "creator": [
    {
      "id": "cd6e8ef1-2517-4f55-bfea-ba2dd81e4c55",
      "last_name": "Sterner",
      "first_name": "Werner"
    }
  ]
}

I created a profiles table, where creator_id has a self-referencing one-to-one relationship. Since the referenced id column is unique, I expect the returned row to be an object.

create table public.profiles (
  id uuid not null,
  creator_id uuid not null default auth.uid (),
  first_name text not null,
  last_name text not null,
  constraint profiles_pkey primary key (id),
  constraint profiles_slug_key unique (slug),
  constraint profiles_id_key unique (id),
  constraint profiles_id_fkey foreign KEY (id) references auth.users (id) on delete CASCADE,
  constraint profiles_creator_id_fkey foreign KEY (creator_id) references profiles (id)
) TABLESPACE pg_default;

const { data, error } = await supabase.from('profiles').select(
  '*, creator:profiles!creator_id(id, first_name, last_name)'
)

Instead, the creator is returned as an array of objects:

{
  "id": "c2b63d53-30a9-4bca-9a41-d769e8d0a8ba",
  "first_name": "Hannah",
  "last_name": "Montana",
  "creator_id": "cd6e8ef1-2517-4f55-bfea-ba2dd81e4c55",
  "creator": [
    {
      "id": "cd6e8ef1-2517-4f55-bfea-ba2dd81e4c55",
      "last_name": "Sterner",
      "first_name": "Werner"
    }
  ]
}

• postgresql
• supabase

Share Improve this question Follow asked Mar 25 at 10:49 peperolipeperoli 10955 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

As it seems, it's not necessary to specify the table name with !profiles if self-referencing. When using '*, creator:creator_id(id, first_name, last_name)', it works as expected.