Supabase で Join Array
使うもの
- Supabase
- Supabase Database Functions
Foreign Key Array
まず、PostgreSQL は、Foreign Key Array には対応していません。
そのため、Supabase も勿論対応していないことになります。
そういった理由から、UUID の Array として情報を持つようにテーブル設計します。
今回では以下のように、シンプルなテーブル構成を使用します。
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', '[email protected]'),
('71b4fee4-139a-44fe-98de-780064bea81e', 'Fuga', '[email protected]');
Success. No rows returned と表示されれば成功です。
サイドバーから Table Editor に移動すると、以下の画像のように、それぞれのテーブルとレコードが作成されていることが確認できます。
files table
users table
RLS はややこしくなってしまうため、一旦オフのままにしています。
本番運用する前には、オンにすることをおすすめします。
uuid_generate_v4() というものを使用していますが、こちらは、uuid-ossp という Extension の関数です。
デフォルトで有効になっているため、特に設定することなく使用できます。
他の 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;
上記のクエリは、以下の記事を参考にしました。ありがとうございます。
すると、以下の画像のように、owner_contents カラムの中に、Array の中身が展開された状態で入っています。
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 しようとすると、エラーになってしまいます。
select id from get_files();
a column definition list is required for functions returning "record"
また、supabase client のドキュメントに記載されている filter を指定できませんでした。
これは、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 をクリックして、削除してください。
SQL Editor へ戻り、先程のクエリを再度実行してみてください。
すると、Success. No rows returned と表示され、上手く関数が作成されました。
owner_contents は Join したカラムになりますが、こちらの型を json として定義することが大切です。
こちらを、json[] として定義するとエラーになってしまいます。
上記関数が作成された上で、下記のクエリを実行してみます。
select id from get_files();
すると、以下の画像のように、エラーになることなく id のみが返ってくることが確認できます。
また、以下を実行すると、setof record として定義していた関数とは異なり、キレイな形でレスポンスが返ってきます。
select * 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":"[email protected]"
},
{
"id":"71b4fee4-139a-44fe-98de-780064bea81e",
"name":"Hoge",
"email":"[email protected]"
}
]
}
最後に
以上で完成になります。
無駄に for 文で回す必要もなく、それでいてフィルターをかけられるため、非常に便利な関数に仕上がったと思います。
SQL の create function は、返り値型が間違っていることしか教えてくれないため、解決までかなり四苦八苦しました。
Foreign Key Array を使用しようと考える前に、中間テーブルを用意することの方がユースケースとしては多いと思いますが、参考になれば幸いです。
Author And Source
この問題について(Supabase で Join Array), 我々は、より多くの情報をここで見つけました https://zenn.dev/matken/articles/supabase-join-array著者帰属：元の著者の情報は、元のURLに含まれています。著作権は原作者に属する。
Collection and Share based on the CC protocol