Query across foreign tables

[florian-lefebvre]

I'm new to Postgres and I have only basic knowledge about SQL.
Here is my schema:

-- CREATE TYPES
create type public.user_status as enum ('ONLINE', 'OFFLINE');


-- CREATE TABLES
create table if not exists public.users (
  id uuid references auth.users on delete cascade not null primary key,
  name text not null,
  description varchar(320),
  avatar_url text,
  status user_status default 'OFFLINE'::public.user_status,

  constraint username_validation check (char_length(name) >= 3)
);

create table if not exists public.organizations (
  id uuid not null primary key DEFAULT uuid_generate_v4(),
  created_at timestamp with time zone default timezone('utc' :: text, now()) not null,
  name text not null,
  url text not null,
  description varchar(320)
);

create table if not exists public.permissions (
  id uuid not null primary key DEFAULT uuid_generate_v4(),
  name text not null
);

create table if not exists public.roles (
  id uuid not null primary key DEFAULT uuid_generate_v4(),
  name text not null,
  organization_id uuid references public.organizations on delete cascade not null
);

create table if not exists public.roles_permissions (
  id uuid not null primary key DEFAULT uuid_generate_v4(),
  permission_id uuid references public.permissions on delete cascade not null,
  status boolean not null DEFAULT false,
  role_id uuid references public.roles on delete cascade not null
);


create table if not exists public.members (
  id uuid not null primary key DEFAULT uuid_generate_v4(),
  organization_id uuid references public.organizations on delete cascade not null,
  user_id uuid references public.users on delete cascade not null,
  role_id uuid references public.roles on delete cascade not null
);


-- CREATE VIEWS
create or replace view organization_details as
select
  organizations.id,
  organizations.name,
  organizations.url,
  organizations.description,
  organizations.created_at,
  users.id as user_id,
  users.name as user_name,
  users.description as user_description,
  users.avatar_url as user_avatar_url,
  users.status as user_status
from organizations
join members on organization_id = members.organization_id
join users on members.user_id = users.id;


-- CREATE FUNCTIONS
-- create function getallusers()
-- returns setof users as
-- $$
--   select * from users;
-- $$ language sql immutable;



-- CREATE POLICIES

-- public.users
comment on table public.users is 'Holds all of users profile information';
alter table public.users enable row level security;
create policy "Public profiles are viewable by everyone." on public.users for select using (true);
create policy "Can insert user" on public.users for insert with check (auth.uid() = id);
create policy "Can update user" on public.users for update using (auth.uid() = id) with check (auth.uid() = id);
create policy "Can delete user" on public.users for delete using (auth.uid() = id);


-- public.organizations
comment on table public.organizations is 'Holds all of organizations information';

alter table public.organizations enable row level security;
create policy "Organizations are viewable by everyone." on public.organizations for select using (true);
create policy "Can insert organization" on public.organizations for insert with check (auth.role() = 'authenticated');
create policy "Can update organization" on public.organizations for update using (auth.role() = 'authenticated');
create policy "Can delete organization" on public.organizations for delete using (auth.role() = 'authenticated');


--public.members
comment on table public.members is 'Holds all of organizations users';

alter table public.members enable row level security;
create policy "Organizations users are viewable by everyone." on public.members for select using (true);
create policy "Can insert organization users" on public.members for insert with check (auth.role() = 'authenticated');
create policy "Can delete organization users" on public.members for delete using (auth.role() = 'authenticated');

Currently, doing

const { data } = await supabase.from("organization_details").select("*");

returns

[
   {
      "id":"1cdd7a02-aa39-4661-b693-5df948ae06cd",
      "name":"Acme",
      "url":"acme",
      "description":null,
      "created_at":"2021-05-22T16:06:39.277298+00:00",
      "user_id":"1f599968-3105-45fb-bf50-6306a4d95a0b",
      "user_name":"User 1",
      "user_description":null,
      "user_avatar_url":null,
      "user_status":"OFFLINE"
   },
   {
      "id":"1cdd7a02-aa39-4661-b693-5df948ae06cd",
      "name":"Acme",
      "url":"acme",
      "description":null,
      "created_at":"2021-05-22T16:06:39.277298+00:00",
      "user_id":"54fa4ea2-8585-4642-8159-b2251852862f",
      "user_name":"User 2",
      "user_description":null,
      "user_avatar_url":null,
      "user_status":"OFFLINE"
   }
]

I would to create a view to get data like this:

[
    {
        "id": "1cdd7a02-aa39-4661-b693-5df948ae06cd",
        "name": "Acme",
        "url": "acme",
        "members": [
            {
                "id": "1f599968-3105-45fb-bf50-6306a4d95a0b",
                "name": "User 1"
            },
            {
                "id": "54fa4ea2-8585-4642-8159-b2251852862f",
                "name": "User 2"
            }
        ]
    }
]

I don't know if that's possible in a view, or possible at all.

[florian-lefebvre]

I think I'm confusing SQL with the auto-generated api

[soedirgo]

Hi @florian-lefebvre, no need for a view to query across foreign tables, you can do something like this:

const { data, error } = await supabase
    .from('organizations')
    .select('*,members:users(*)')
// console.log(JSON.stringify(data, null, 2))
//
// [
//   {
//     "id": "37fe573e-11e6-4c11-9338-be1a670f53ce",
//     "created_at": "2021-05-22T22:36:35+00:00",
//     "name": "Acme",
//     "url": "acme",
//     "description": null,
//     "members": [
//       {
//         "id": "1f599968-3105-45fb-bf50-6306a4d95a0b",
//         "name": "User 1",
//         "description": null,
//         "avatar_url": null,
//         "status": "OFFLINE"
//       },
//       {
//         "id": "54fa4ea2-8585-4642-8159-b2251852862f",
//         "name": "User 2",
//         "description": null,
//         "avatar_url": null,
//         "status": "OFFLINE"
//       }
//     ]
//   }
// ]

[florian-lefebvre]

Okay I managed to get it work:

const { data, error } = await supabase
      .from("organizations")
      .select(`
        *,
        members(
          user:users(
            id,
            name
          ),
          role:roles(
            name,
            permissions:roles_permissions(
              status,
              permissions(
                name
              )
            )
          )
        )
      `);

[                                                                                                                                                                                                                                  11:11:51
  {
    "id": "1cdd7a02-aa39-4661-b693-5df948ae06cd",
    "created_at": "2021-05-23T08:53:28.803018+00:00",
    "name": "Acme",
    "url": "acme",
    "description": null,
    "members": [
      {
        "user": {
          "id": "1f599968-3105-45fb-bf50-6306a4d95a0b",
          "name": "User 1"
        },
        "role": {
          "name": "ADMIN",
          "permissions": [
            {
              "status": true,
              "permissions": {
                "name": "View settings"
              }
            }
          ]
        }
      },
      {
        "user": {
          "id": "54fa4ea2-8585-4642-8159-b2251852862f",
          "name": "User 2"
        },
        "role": {
          "name": "MEMBER",
          "permissions": [
            {
              "status": false,
              "permissions": {
                "name": "View settings"
              }
            }
          ]
        }
      }
    ]
  }
]

But is there any way to get the following @soedirgo ?

[                                                                                                                                                                                                                                  11:15:05
    {
      "id": "1cdd7a02-aa39-4661-b693-5df948ae06cd",
      "created_at": "2021-05-23T08:53:28.803018+00:00",
      "name": "Acme",
      "url": "acme",
      "description": null,
      "members": [
        {
          "id": "1f599968-3105-45fb-bf50-6306a4d95a0b",
          "name": "User 1",
          "role": {
            "name": "ADMIN",
            "permissions": [
              {
                "status": true,
                "name": "View settings"
              }
            ]
          }
        },
        {
          "id": "54fa4ea2-8585-4642-8159-b2251852862f",
          "name": "User 2",
          "role": {
            "name": "MEMBER",
            "permissions": [
              {
                "status": false,
                "name": "View settings"
              }
            ]
          }
        }
      ]
    }
  ]

[soedirgo]

Closest I've got is:

const { data, error } = supabase
    .from('organizations')
    .select(`
        *, members:users(
            id, name, role:roles(
                name, permissions:roles_permissions(
                    status, permissions(name)
                )
            )
        )
    `)

Which gives:

[
  {
    "id": "1cdd7a02-aa39-4661-b693-5df948ae06cd",
    "created_at": "2021-05-23T23:24:59+00:00",
    "name": "Acme",
    "url": "acme",
    "description": null,
    "members": [
      {
        "id": "1f599968-3105-45fb-bf50-6306a4d95a0b",
        "name": "User 1",
        "role": [
          {
            "name": "ADMIN",
            "permissions": [
              {
                "status": true,
                "permissions": {
                    "name": "View settings"
                }
              }
            ]
          }
        ]
      },
      {
        "id": "54fa4ea2-8585-4642-8159-b2251852862f",
        "name": "User 2",
        "role": [
          {
            "name": "MEMBER",
            "permissions": [
              {
                "status": false,
                "permissions": {
                    "name": "View settings"
                }
              }
            ]
          }
        ]
      }
    ]
  }
]

Save from creating a view or molding the response client-side, you can try moving members.role_id to users.role (so users and roles are many-to-one) and making permissions an enum (like public.user_status) to get exactly what you want.

[florian-lefebvre]

Okay, thanks a lot for your help !

[florian-lefebvre]

Is it possible to make filters based on the relationships ?

const { data, count, error } = await this.supabase
      .from("organizations")
      .select(
        `
      *, members:users(
          id, name, role:roles(
              name, permissions:roles_permissions(
                  status, details:permissions(name)
              )
          )
      ), roles!organization_id(
          name, permissions:roles_permissions(
              status, details:permissions(
                  name
              )
          )
      )
    `,
        { count: "exact" }
      )
      .filter("members.name", "eq", "User 3");

Because doing that, I get

{
  "data": [
    {
      "id": "1cdd7a02-aa39-4661-b693-5df948ae06cd",
      "name": "Acme",
      "url": "acme",
      "description": null,
      "created_at": "2021-05-23T14:36:34.239908+00:00",
      "updated_at": "2021-05-23T14:36:34.239908+00:00",
      "members": [],
      "roles": [
        {
          "name": "ADMIN",
          "permissions": [
            {
              "status": true,
              "details": {
                "name": "View settings"
              }
            }
          ]
        },
        {
          "name": "MEMBER",
          "permissions": [
            {
              "status": false,
              "details": {
                "name": "View settings"
              }
            }
          ]
        }
      ]
    }
  ],
  "count": 1
}

and no an empty array @soedirgo

[soedirgo]

Were you expecting data to be []? I don't think that's possible, you can do filters, order, etc. on foreign tables, but they don't affect the original table (ref). So in this case a view might work better.