SupabaseS
Supabase•4y ago
iolyd

Wrong cardinality is inferred

I have two tables, public.users and public.users_roles, that are set up like so:
CREATE TABLE public.users (
  id uuid NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(),
  updated_by_id uuid NOT NULL,
  first_name text NULL,
  CONSTRAINT users_pkey PRIMARY KEY (id),
  CONSTRAINT users_un UNIQUE (id)
);

CREATE TABLE public.users_roles (
  user_id uuid NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now(),
  "role" public."user_role" NOT NULL DEFAULT
  updated_by_id uuid NOT NULL DEFAULT default_uid(),
  CONSTRAINT users_roles_pkey PRIMARY KEY (user_id),
  CONSTRAINT users_roles_un UNIQUE (user_id),
  CONSTRAINT users_roles_updated_by_id_fkey FOREIGN KEY (updated_by_id) REFERENCES public.users(id),
  CONSTRAINT users_roles_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE ON UPDATE CASCADE
);

Because of the updated_by_id columns, postgrest requires that I disambiguate the target when doing a join. This is fine and can be done easily with something like:
const profileRes = await db
  .from('users')
  .select(
    `
      *,
      role:users_roles!users_roles_user_id_fkey(
        *
      )
    `
  )
  .eq('id', session.user.id)
  .single();

The thing is, postgrest(?) sees a one-to-many cardinality where it logically should be one-to-one based off the constraints. I also think this prevents the newly added automatic single() on join feature to take effect. Is there a way to enforce the right cardinality? Am I missing a constraint?
Was this page helpful?