How to convert an array with single item to object for embedded resource?

[hambergerpls]

Environment

• PostgreSQL version: supabase/supabase:15.1.0.90
• PostgREST version: postgrest/postgrest:v11.1.0
• Operating system: WSL 2

Description of issue

Hi, I'm trying to fetch a user's role where

1. roles has one-to-one many-to-one relationship with organizations (edited)
2. users has one-to-many relationship with organizations
3. users has one-to-many relationship with roles

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)

GET /users?select=*,role:roles!rel_roles_users(*)&auth_id=eq.f35334a3-7b90-48fa-8df1-b97445583c88&roles.organisation_id=eq.ogn_x1w5j2hgpkt7jz155aubbxin
Accept: application/vnd.pgrst.object+json

[laurenceisla]

Hi, I'm trying to fetch a user's role where

1. roles has one-to-one relationship with organizations
2. users has one-to-many relationship with organizations
3. users has one-to-many relationship with roles

Hi, it looks like the table rel_roles_users is incompatible with these relationships you're defining. By having role_id, user_id, organization_id as primary keys you're making Many-To-Many relationships between roles->users and roles->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 composite primary key. Maybe keep role_id as the only primary key, and add other unique constraint for role_id,organisation_id. Or, in that case, just use public.roles directly instead of public.rel_roles_users, it would be easier to build your relationships like that.

Now for the PostgREST query, as it is right now, you can get what you're asking by using the spread operator.

GET /rel_roles_users?select=...users(*),roles(*)&users.auth_id=eq.f35334a3-7b90-48fa-8df1-b97445583c88&roles.organisation_id=eq.ogn_x1w5j2hgpkt7jz155aubbxin
Accept: application/vnd.pgrst.object+json

I used rel_roles_users as the main table, because it has a to-one relationship with both users and roles. That is why it returns a single resource for roles and it can "spread" the content of users as if it were that table we're querying.

[hambergerpls]

Oh my mistake! What I meant was that roles has many-to-one relationship with organizations. An organization has many roles, but a role can only belong to one organization.

The PostgREST query is perfect!

Thank you @laurenceisla !