Zenn
📚

Supabase で Join Array

2022/04/12に公開
PostgreSQL
Supabase
tech

使うもの

  • Supabase

https://supabase.com

  • Supabase Database Functions

https://supabase.com/docs/guides/database/functions

Foreign Key Array

まず、PostgreSQL は、Foreign Key Array には対応していません。
そのため、Supabase も勿論対応していないことになります。

https://github.com/supabase/supabase/discussions/535#discussioncomment-285696

そういった理由から、UUID の Array として情報を持つようにテーブル設計します。
今回では以下のように、シンプルなテーブル構成を使用します。

Schema
Generated by Supabase Schema

上図では、owners が実質的な Foreign Key Array となります。
カラム名から分かる通り、users テーブルの ID を UUID の Array として保持するためのカラムになります。

こういった構成の時、owners を for 文で回して、users テーブルから 1 つづつ取得していくのは非常に遅い処理となってしまいます。
そこで、Supabase Database Functions を活用して、Join した状態を返すように実装します。

テーブルとサンプルデータを作成する

まず、今回使用する、テーブルとサンプルデータを作成します。
Supabase Dashboard から、SQL Editor へ移動してください。
そして、以下の内容をコピー＆ペーストし、RUN を実行してください。

create table if not exists files (
    id uuid primary key default uuid_generate_v4(),
    owners uuid[]
);

create table if not exists users (
    id uuid primary key default uuid_generate_v4(),
    name text,
    email text not null
);

insert into files values
    ('3bab501e-99d6-43db-bc8d-bebe8af36cf9', '{"6f13b35e-a2c8-442f-95cb-c2ebf5dc5638", "71b4fee4-139a-44fe-98de-780064bea81e"}');

insert into users values
    ('6f13b35e-a2c8-442f-95cb-c2ebf5dc5638', 'Hoge', 'hoge@example.com'),
    ('71b4fee4-139a-44fe-98de-780064bea81e', 'Fuga', 'fuga@example.com');

Success. No rows returned と表示されれば成功です。

サイドバーから Table Editor に移動すると、以下の画像のように、それぞれのテーブルとレコードが作成されていることが確認できます。

Files Table
files table

Users Table
users table

RLS はややこしくなってしまうため、一旦オフのままにしています。
本番運用する前には、オンにすることをおすすめします。

uuid_generate_v4() というものを使用していますが、こちらは、uuid-ossp という Extension の関数です。
デフォルトで有効になっているため、特に設定することなく使用できます。

https://supabase.com/docs/guides/database/extensions/uuid-ossp#uuid_generate_v4

他の Extensions に興味がある方は、Supabase Dashboard > Database > Extensions から確認できます。

Array の中身を Join するクエリを書く

続いて、先程用意したサンプルデータに対して、Array の中身を Join するクエリを実行します。

まず、Supabase Dashboard から SQL Editor へアクセスし、New query ボタンをクリックして、新しいクエリを用意します。
そして、以下の内容をコピー＆ペーストし、RUN をクリックしてください。

select * from files
left outer join lateral (
  select json_agg(files_attr) as owner_contents
  from (
    select *
    from unnest(owners) as arr(id)
    join users using(id)
  ) as files_attr
) as owner_contents on true;

上記のクエリは、以下の記事を参考にしました。ありがとうございます。

https://qiita.com/nishimura/items/575e642503139229059a

すると、以下の画像のように、owner_contents カラムの中に、Array の中身が展開された状態で入っています。

Join Result

PostgreSQL Function として定義する

以下を、SQL Editor 内にペーストし、RUN をクリックしてください。
Success. No rows returned と表示されれば、関数が作成されています。

create function get_files()
returns setof record
language sql
as $$
  select * from files
  left outer join lateral (
    select json_agg(files_attr) as owner_contents
    from (
      select *
      from unnest(owners) as arr(id)
      join users using(id)
    ) as files_attr
  ) as owner_contents on true;
$$;

上手くいっていれば、関数を叩けるようになっているので、以下を実行してみます。

select get_files();

すると、以下のような結果が返ってくることが確認できます。

Select get_files

変な形式に変わってはいますが、一応正しいデータが返ってきています。
しかし、例えば以下のように Select しようとすると、エラーになってしまいます。

select id from get_files();

a column definition list is required for functions returning "record"

また、supabase client のドキュメントに記載されている filter を指定できませんでした。

https://supabase.com/docs/reference/javascript/rpc#with-filters

これは、record だと返り値型としては不充分で、正しく本来返されるはずの型を網羅する必要があるためです。
これではあまり使い心地が良いものではないので、できるようにしていきます。

返り値型を明記する

返り値型を網羅するには、以下のように記述します。
全て書いていくしかないため、フィールドが多いテーブルの場合は、かなり大変かと思います。

create or replace function get_files()
returns table (
  id uuid,
  owners uuid[],
  owner_contents json
)
language sql
as $$
  select
    files.id,
    files.owners,
    owner_contents
  from files
  left outer join lateral (
    select json_agg(files_attr) as owner_contents
    from (
      select *
      from unnest(owners) as arr(id)
      join users using(id)
    ) as files_attr
  ) as owner_contents on true;
$$;

このまま RUN をクリックしても、以下のエラーが表示されてしまいます。

cannot change return type of existing function

一度定義した関数の返り値型を、後から変更できないためです。
そのため、一旦関数を削除します。

SQL Editor 経由で関数を作成した場合でも、Supabase Dashboard > Database > Functions と移動することで、以下の画像のように GUI として確認できます。
Delete function をクリックして、削除してください。

Function List

SQL Editor へ戻り、先程のクエリを再度実行してみてください。
すると、Success. No rows returned と表示され、上手く関数が作成されました。

owner_contents は Join したカラムになりますが、こちらの型を json として定義することが大切です。
こちらを、json[] として定義するとエラーになってしまいます。

上記関数が作成された上で、下記のクエリを実行してみます。

select id from get_files();

すると、以下の画像のように、エラーになることなく id のみが返ってくることが確認できます。

Select id from get_files

また、以下を実行すると、setof record として定義していた関数とは異なり、キレイな形でレスポンスが返ってきます。

select * from get_files();

Select all from get_files

supabase client から関数を叩く

以下のように、rpc を用いて Supabase Database Function を叩きます。

FileType の定義例

type UUID = string;

interface User {
    id: UUID;
    name?: string;
    email: string;
}

interface FileType {
    id: UUID;
    owners?: UUID[];
    owner_contents?: User[];
}

const { data, error } = await supabaseClient
    .rpc<FileType>("get_files")
    .eq("id", "3bab501e-99d6-43db-bc8d-bebe8af36cf9")
    .limit(1)
    .single();

if (!error && data) {
    console.log(data);
}

すると、以下のようなレスポンスが返ってくることが確認できます。

{
    "id": "3bab501e-99d6-43db-bc8d-bebe8af36cf9",
    "owners": [
        "6f13b35e-a2c8-442f-95cb-c2ebf5dc5638",
        "71b4fee4-139a-44fe-98de-780064bea81e"
    ],
    "owner_contents": [
        {
            "id":"6f13b35e-a2c8-442f-95cb-c2ebf5dc5638",
            "name":"Fuga",
            "email":"fuga@example.com"
        },
        {
            "id":"71b4fee4-139a-44fe-98de-780064bea81e",
            "name":"Hoge",
            "email":"hoge@example.com"
        }
    ]
}

最後に

以上で完成になります。
無駄に for 文で回す必要もなく、それでいてフィルターをかけられるため、非常に便利な関数に仕上がったと思います。

SQL の create function は、返り値型が間違っていることしか教えてくれないため、解決までかなり四苦八苦しました。

Foreign Key Array を使用しようと考える前に、中間テーブルを用意することの方がユースケースとしては多いと思いますが、参考になれば幸いです。

GitHubで編集を提案

Discussion