How to convert an array with single item to object for embedded resource? #2917
-
Environment
Description of issueHi, I'm trying to fetch a user's role where
I have a rel_roles_users table described below CREATE TABLE
public.rel_roles_users (
role_id TEXT NOT NULL,
user_id TEXT NOT NULL,
organisation_id TEXT NOT NULL,
CONSTRAINT rel_roles_users_pkey PRIMARY KEY (role_id, user_id, organisation_id),
CONSTRAINT rel_roles_users_key UNIQUE (user_id, organisation_id),
CONSTRAINT rel_roles_users_role_id_fkey FOREIGN KEY (role_id) REFERENCES public.roles (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT rel_roles_users_user_id_fkey FOREIGN KEY (user_id) REFERENCES public.users (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT rel_roles_users_organisation_id_fkey FOREIGN KEY (organisation_id) REFERENCES public.organisations (id) ON UPDATE CASCADE ON DELETE CASCADE
) TABLESPACE pg_default; Basically users can only have one role in an organization but can have many organizations. I'm not sure if this is the right approach to implement the relationship, I appreciate any suggestions and recommendations. Anyways, I read from the docs that we can change the response to be singular or plural, but it doesn't apply to embedded resources as it seems. How I resolve it from the client currently is to take the first item in the list, which I really don't like, because the rest of my deserializers can deserialize one-to-one relationships directly from a map without the extra steps. (Expected behavior vs actual behavior) Expected behavior
{
"id" : "user_1",
"name" : "User 1",
"role" : {
"id" : "role_1",
"name" : "Role 1",
"organization_id" : "org_1"
}
} Actual behavior
{
"id" : "user_1",
"name" : "User 1",
"role" : [
{
"id" : "role_1",
"name" : "Role 1",
"organization_id" : "org_1"
}
]
} (Steps to reproduce: Include a minimal SQL definition plus how you make the request to PostgREST and the response body)
|
Beta Was this translation helpful? Give feedback.
Replies: 1 comment 1 reply
-
Hi, it looks like the table So, yes, I think you'll need to restructure your tables. You could keep Now for the PostgREST query, as it is right now, you can get what you're asking by using the spread operator.
I used |
Beta Was this translation helpful? Give feedback.
Hi, it looks like the table
rel_roles_users
is incompatible with these relationships you're defining. By havingrole_id, user_id, organization_id
as primary keys you're making Many-To-Many relationships betweenroles->users
androles->organizations
. Also I'm a bit confused about rule N°1, if a 'role_a' belongs to 'org_a' then 'org_a' cannot have any other roles, is that expected?So, yes, I think you'll need to restructure your tables. You could keep
public.rel_roles_users
but without a comp…