一、开篇
关联查询是使用数据库进行应用开发时经常使用的场景，Supabase 基于 PostgreSQL 构建其生态体系，不可避免的要对关联查询进行支持。Supabase 的关联查询能力依赖于 PostgREST 服务提供的基础能力，最近的几个版本中，PostgREST 在关联查询场景中进行了不少的优化，因此本篇文章会结合 PostgREST 对 Supabase 关联查询进行较为详尽的解释，然后结合具体的例子，通过 Supabase JavaScript SDK 给出一些具体的代码示例，方便开发者理解和使用 Supabase 进行应用开发。
本文会从如下一些方面进行展开，内容将基本覆盖所有你需要用到的场景：
二、外键与多表关联
PostgREST 服务在启动时会加载数据库中的元数据信息（catalog），并通过分析元数据信息来构建对象之间的关联关系。PostgREST 通过外键来识别表与表之间的关系，从而确定两个表是否能进行关联查询，目前 PostgREST 的接口没有实现 on 语法，因此开发者无法简单的根据自己的需要在任意两个表之间进行关联查询。
PostgREST 在启动时会分析表、视图、函数等一系列对象，当 join 表时，PostgREST 会将表的外键字段作为关联条件；当 join 视图时，会将该视图对应的表的外键作为关联条件；当 join 函数时，会将函数所返回的表的外键字段作为关联条件。
这里这么描述可能比较抽象，后面会详细展开用具体的例子进行讲解，只需要知道如下两点：
接下来我们用实际例子来详细讲解一下如何解决各种场景下的关联查询需求。
三、关联关系与关联查询
下面是一个简单的场景的 ER 图，来源于 PostgREST，该场景描述了电影、导演、演员、角色、技术、提名、竞争对手等表之间的关联关系：
3.1 多对一关系
假设多个电影的导演可能是同一个人，但一个电影的导演不能是多个人，那么 films 表和 directors 表之间存在多对一关系，可以如下方式建表：
create table directors( id serial primary key, first_name text, last_name text);
create table films( id serial primary key, director_id int references directors(id), title text, year int, rating numeric(3,1), language text);
复制代码
也就是如下图所示的关系：
如果我们要查询所有电影的 title 以及每部电影的导演名字，可以如下方式进行关联查询：
const { data, error } = await supabase .from('films') /* films: 要查询的表 */ .select(` title, directors( last_name ) `)
复制代码
可以看到，主表 films 作为参数传给了 from 函数。关联表 directors 及要查询的字段全部写在 select 函数中，其中 title 是 films 的字段， directors( last_name ) 描述了要从关联表 directors 取 last_name 字段。
关联查询的条件为: films.director_id = directors.id，该条件由 PostgREST 服务自动添加。返回数据的格式如下：
[ { "title": "Workers Leaving The Lumière Factory In Lyon", "directors": { "last_name": "Lumière" } }, { "title": "The Dickson Experimental Sound Film", "directors": { "last_name": "Dickson" } }]
复制代码
注意，由于是多对一关系，所以返回结果中每一项的 directors 的值是一个 json 对象，而不是 json 数组。
3.2 一对多关系
上述关系反过来 directors->films 就是一对多关系，关联查询方法类似：
const { data, error } = await supabase .from('directors') /* directors: 要查询的表 */ .select(` last_name, films( title ) `)
复制代码
上面的主表变成了directors ，关联表变成了films ，由于是一对多关系，返回结果的 films 的值变成了 json 数组：
[ { "last_name": "Lumière", "films": [ {"title": "Workers Leaving The Lumière Factory In Lyon"} ] }, { "last_name": "Dickson", "films": [ {"title": "The Dickson Experimental Sound Film"} ] }]
复制代码
3.3 多对多关系
多对多关系不能直接在两个表之间用外键约束进行表达，要表达多对多关系，需要一张额外的表，该表需要包含两个外键约束，分别关联到不同的表，本例中，roles 表就在 films 表和 actors 表之间建立了多对多关系，如下图所示：
相关建表语句如下：
create table actors( id serial primary key, first_name text, last_name text);
create table roles( film_id int references films(id), actor_id int references actors(id), character text, primary key(film_id, actor_id));
复制代码
roles表有两个外键，分别关联到 films 表和 actors 表，而 films 和 actors 表之间并没有外键关联关系，PostgREST 可以识别到这种情况， 因此我们在编写代码时，可以直接关联 films 和 actors 表，他们内部之间如何借助 roles 表完成关联，则由 PostgREST 在服务端自动完成。
例如：查询所有演员以及其参演的电影，可以如下方式编写代码：
const { data, error } = await supabase .from('actors') /* actors: 要查询的表 */ .select(` first_name, last_name, films( title ) `)
复制代码
3.4 一对一关系
一对一关系表示外键约束的两端在自己的表中都是唯一的，通常对应字段是 primary key 或者有 unique 约束，比如：
technical_specs 的建表语句如下：
CREATE TABLE technical_specs( film_id INT REFERENCES films UNIQUE, runtime TIME, camera TEXT, sound TEXT);
复制代码
film_id 在 technical_specs 表中是唯一的，而 films.id 是 primary key ，也是唯一的，因此这两个表是一对一关系。对于 PostgREST 来说，只要关联关系的对端是 对一 关系，查询的返回结果的对应值就是 JSON 对象，只要是 对多 关系，查询的返回结果的对应值就是 JSON数组。
当要查询所有电影，以及电影使用的摄像机类型时，可以如下方式进行关联查询：
const { data, error } = await supabase .from('films') /* films: 要查询的表 */ .select(` title, technical_specs( camera ) `)
复制代码
返回结果如下：
[ { "title": "Pulp Fiction", "technical_specs": {"camera": "Arriflex 35-III"} }, ".."]
复制代码
这一小节介绍了最基本也是最常用的关联查询方法，从上面几个示例可以看到，只要在表与表之间直接或间接建立了外键关联，在编写代码时就可以对这两个表进行关联查询，从多对多的例子中，我们看到客户端代码甚至不需要显示的通过中间表进行关联，PostgREST 后台会自动处理好处理，让代码编写变的简单直接。
关联查询的代码编写方法也很简单，主表放在 from 函数中作为参数，关联表要查询的字段用表名包裹后放到 select 函数中作为参数即可。
3.5 如果两个表之间存在多个外键关联关系怎么办
像下面这种：
订单表的 billing_address_id 和 shipping_address_id 都跟 addresses 表有关联关系，此时如果直接对这两个表进行管理查询会报错，类似下面这种错误：
代码：
const { data, error } = await supabase .from('orders') .select(` *, addresses(*) `)
复制代码
返回：
{ "code": "PGRST201", "details": [ { "cardinality": "many-to-one", "embedding": "orders with addresses", "relationship": "billing using orders(billing_address_id) and addresses(id)" }, { "cardinality": "many-to-one", "embedding": "orders with addresses", "relationship": "shipping using orders(shipping_address_id) and addresses(id)" } ], "hint": "Try changing 'addresses' to one of the following: 'addresses!billing', 'addresses!shipping'. Find the desired relationship in the 'details' key.", "message": "Could not embed because more than one relationship was found for 'orders' and 'addresses'"}
复制代码
为了正确关联这两个表，需要在建表的时候明确指定外键名称，并在关联查询代码中使用该名称进行关联查询。我们先看一下表定义时如何设置外键名称：
create table addresses ( id serial primary key, name text, city text, state text, postal_code char(5));
create table orders ( id serial primary key, name text, billing_address_id int, shipping_address_id int,
constraint billing foreign key(billing_address_id) references addresses(id), constraint shipping foreign key(shipping_address_id) references addresses(id));
复制代码
上面的 billing 和 shipping 就是两个外键的名字，如果不指定名称，数据库会为我们生成一个名称，这里为了能让我们准确的控制代码如何编写，手动指定外键约束的名称。
正确的代码写法：
const { data, error } = await supabase .from('orders') .select(` name, billing_address:addresses!billing(name), shipping_address:addresses!shipping(name) `)
复制代码
返回结果：
[ { "name": "Personal Water Filter", "billing_address": { "name": "32 Glenlake Dr.Dearborn, MI 48124" }, "shipping_address": { "name": "30 Glenlake Dr.Dearborn, MI 48124" } }]
复制代码
同样的，我们也可以根据 addresses 表来查询有哪些订单是与该地址相关联的：
const { data, error } = await supabase .from('addresses') .select(` name, billing_orders:orders!billing(name), shipping_orders!shipping(name) `) .eq('id', 1)
复制代码
该查询的返回结果示例：
[ { "name": "32 Glenlake Dr.Dearborn, MI 48124", "billing_orders": [ { "name": "Personal Water Filter" }, { "name": "Coffee Machine" } ], "shipping_orders": [ { "name": "Coffee Machine" } ] }]
复制代码
上面的例子中我们用到了 eq 过滤条件，后面会介绍。
3.6 递归关联
有一类比较特殊的外键关联关系，就是自己关联自己：
上图中 presidents 表的 predecessor_id 指向的是当前表的 id 字段，这种情况我们称之为递归关联。递归关联要想在客户端代码中执行关联查询，需要依赖 function ，在下一小结介绍完 function 的使用方法之后，我们再回过头来看看这个问题怎么解决。
四、没有外键关系的两个表进行关联查询
有的时候，我们没有办法在两个表之间建立外键关联，可能是因为历史原因，也可能是技术上不可行（比如 FDW 表），但业务上又需要在两个表之间进行关联查询，这时候怎么办呢。此时可以通过 function 手动建立关联关系。
4.1 如何使用 function
假设我们有一个表是从 csv 文件中导入的，建表语句如下：
create foreign table premieres ( id integer, location text, "date" date, film_id integer) server import_csv options ( filename '/tmp/directors.csv', format 'csv');
复制代码
为了在 premieres 和 films 表之间建立关联关系，从而允许开发人员通过 supabase SDK 对这两个表进行关联查询，我们可以创建如下 function ：
create function film(premieres) returns setof films rows 1 as $$ select * from films where id = $1.film_id$$ stable language sql;
复制代码
该 function 的输入参数是表 premieres ，返回结果是表 films ，关联条件是：
select * from films where id = $1.film_id
复制代码
上面的 film 函数就在 premieres 和 films 表之间手动建立了关联关系。rows 1 则表明对于一个确定的输入只有一条返回数据，表明该关联关系是 多对一 关系，SDK 返回的结果是 json对象 。
通过 function 手动建立关联关系后，如何在程序中进行关联查询呢，代码写法如下：
const { data, error } = await supabase .from('premieres') .select(` location, film(title) `)
复制代码
上面的代码中，我们可以看到，premieres 是我们要查询的表，原本我们业务逻辑上是要关联 films 表进行关联查询，但是由于两个表之间没有外键关联，无法直接完成关联查询。这里我们在 select 函数中传入的不再是 films 表，而是 film 函数，该函数返回的是 films 表，所以，PostgREST 内部会帮我们做好转换，并成功完成最终的关联查询。
另外提一句，film 这个函数名字可以根据自己的需要命名，并不是因为要关联的表是 films 就必须命名为 film 。
通过上面的函数定义，我们业务上可以实现通过 premieres 表关联 films 表，如果业务需要通过 films 表关联 premieres 表呢，需要额外定义关联关系吗，答案是要。
所以一般，我们需要定义一对 function 双向关联两个表。上面两个表的反向关联定义如下，这是一个一对多的关联关系：
create function premieres(films) returns setof premieres as $$ select * from premieres where film_id = $1.id$$ stable language sql;
复制代码
客户端关联查询代码的写法如下：
const { data, error } = await supabase .from('films') .select(` title, premieres(location) `)
复制代码
像 C++/Java 等面向对象编程语言一样，function 可以重载，以实现用同一个函数名进行多个不同表之间的关联，下面是一个例子：
create function directors(films) returns setof directors rows 1 as $$ select * from directors where id = $1.director_id$$ stable language sql;
create function directors(film_schools) returns setof directors as $$ select * from directors where film_school_id = $1.id$$ stable language sql;
复制代码
第一个函数定义了 films->directors 的关联关系，第二个函数定义了 film_schools->directors 的关联关系。PostgREST 会自动识别这种重载。重载的好处是，我们可以在编写客户端代码时，对同一个目标表可以使用同一个函数进行关联查询。
上面的例子中，目标表都是 directors ，所以我们在编写客户端代码时不论是 films 还是 film_schools 表要关联该目标表，都可以使用同一个关联函数：
const { data, error } = await supabase .from('films') .select(` title, directors(last_name) `)
const { data, error } = await supabase .from('film_schools') .select(` school_name, directors(last_name) `)
复制代码
使用 function 手动构建两个表之间的关联关系时，需要特别注意如下一些约束：
关于 PostgreSQL 的函数内联，可以看一下官方文档的描述。
4.2 如何解决递归关联问题
上面我们遗留了一个问题，如果表的外键指向自己，怎么进行关联查询：
此时我们需要创建一对 function 来手动进行关联，可以看到两个函数都是返回 rows 1 ，表示两者之间存在一对一关系：
create or replace function predecessor(presidents) returns setof presidents rows 1 as $$ select * from presidents where id = $1.predecessor_id$$ stable language sql;
create or replace function successor(presidents) returns setof presidents rows 1 as $$ select * from presidents where predecessor_id = $1.id$$ stable language sql;
复制代码
此时我们如果要查询某位总统的前任和继任者，可以这么写代码：
const { data, error } = await supabase .from('presidents') .select(` last_name, predecessor(last_name), successor(last_name) `) .eq('id', 2)
复制代码
返回结果示例：
[ { "last_name": "Adams", "predecessor": { "last_name": "Washington" }, "successor": { "last_name": "Jefferson" } }]
复制代码
4.3 如何解决一对多递归关联问题
要创建一对多（多对一）递归关系，下面是一个例子：
主管和员工是一对多关系，员工和主管之间则是多对一关系，需要创建如下一对 function ：
create or replace function supervisees(employees) returns setof employees as $$ select * from employees where supervisor_id = $1.id$$ stable language sql;
create or replace function supervisor(employees) returns setof employees rows 1 as $$ select * from employees where id = $1.supervisor_id$$ stable language sql;
复制代码
4.4 如何解决多对多递归关联问题
多对多也是一个非常常见的递归关联场景，比如我们常见的微博用户之间互相关注，就是一个非常典型的单表数据多对多关联。上面我们在介绍多对多关联时，我们知道两个表之间无法直接用 SQL 表达多对多关系，需要有一个额外的关联关系表，对于单表递归场景的多对多也是类似的，要在用户之间构建多对多关系，需要有一个单独的关注（订阅）表：
建表语句如下：
create table users ( id int primary key generated always as identity, first_name text, last_name text, username text unique);
create table subscriptions ( subscriber_id int references users(id), subscribed_id int references users(id), type text, primary key (subscriber_id, subscribed_id));
复制代码
为了表达多对多关联查询，这里我们也需要借助 function 的能力：
create or replace function subscribers(users) returns setof users as $$ select u.* from users u, subscriptions s where s.subscriber_id = u.id and s.subscribed_id = $1.id$$ stable language sql;
create or replace function following(users) returns setof users as $$ select u.* from users u, subscriptions s where s.subscribed_id = u.id and s.subscriber_id = $1.id$$ stable language sql;
复制代码
subscribers 函数查询出的是用户的粉丝，following 函数查询出的是用户所的关注用户，就好比：
要查询某个用户的粉丝和关注用户，可以如下方式编写代码：
const { data, error } = await supabase .from('users') .select(` username, subscribers(username), following(username) `) .eq('id', 2)
复制代码
返回结果示例如下：
[ { "username": "wuershan", "subscribers": [ { "username": "zhangsan" }, { "username": "lisi" } ], "following": [ { "username": "chenmuchi" } ] }]
复制代码
五、如何在表和视图之间进行关联查询
PostgREST 会根据视图所依赖的表来推断视图与其他表的外键关联关系，视图所依赖的表我们称之为基础表。构建视图时，出现在 FROM 或者 JOIN 表达式中的表都会被 PostgREST 视作基础表。要想让外键在视图中起作用，外键字段必须出现在第一层 select 表达式中，例如，下面的视图包含三个基础表：nominations，films，competitions ：
CREATE VIEW nominations_view AS SELECT films.title as film_title , competitions.name as competition_name , nominations.rank , nominations.film_id as nominations_film_id , films.id as film_id FROM nominations JOIN films ON films.id = nominations.film_id JOIN competitions ON competitions.id = nominations.competition_id;
复制代码
该视图的顶层 Select 中包含了 nominations.film_id ，该字段与 films 表有外键关联关系，因此该视图可以与 films 表之间进行关联查询。同样的，该视图包含了 films.id 字段，因此可以直接跟 roles 表进行关联查询，下面是一个该视图与 films、roles、actors 表进行多表关联查询的例子：
const { data, error } = await supabase .from('nominations_view') .select(` film_title, films(language), roles(character), actors(last_name,first_name) `) .eq('rank', 2)
复制代码
注意：
六、RPC 调用时进行关联查询
该特性目前在 Supabase SDK 中还没有实现。需要通过 RESTful API 进行调用。举例如下：
CREATE FUNCTION getallfilms() RETURNS SETOF films AS $$ SELECT * FROM films;$$ LANGUAGE SQL STABLE;
复制代码
getallfilms 是一个函数，我们可以通过 rpc 调用该函数，由于该函数返回的是 SETOF films，因此可以跟 directors 表进行管理查询：
GET /rpc/getallfilms?select=title,directors(id,last_name)&title=like.*Workers*
复制代码
七、嵌套关联查询
我们直接看一个例子：
const { data, error } = await supabase .from('actors') .select(` last_name, roles(character,films(title,year)) `)
复制代码
上面的代码可以查询演员、他们的角色以及这些角色对应的电影。
八、关联查询时进行条件过滤
我们可以在关联查询时附加额外的 where 条件进行数据过滤，也可以进行排序操作。
8.1 排序
// 按actors.last_name升序排序const { data, error } = await supabase .from('films') .select(`*, actors(*)`) .order('actors.last_name')
// 按actors.last_name降序排序const { data, error } = await supabase .from('films') .select(`*, actors(*)`) .order('actors.last_name', { ascending: false })
// 按actors.last_name降序排序, first_name升序const { data, error } = await supabase .from('films') .select(`*, actors(*)`) .order('actors.last_name', { ascending: false }) .order('actors.first_name')
复制代码
8.2 过滤
supabase 支持绝大部分过滤条件，过滤条件要写在 select 函数调用之后，举例如下：
// in 条件const { data, error } = await supabase .from('films') .select(`*, roles(*)`) .in('roles.character', ['Chico','Harpo','Groucho'])
// or 条件const { data, error } = await supabase .from('films') .select(`*, roles(*)`) .or('roles.character.in.(Chico,Harpo,Groucho)', 'roles.character.eq.Zeppo')
复制代码
更多的过滤条件的用法可以参考官方文档。
8.3 limit
限制返回数据的条数：
const { data, error } = await supabase .from('films') .select(`*, roles(*)`) .limit(10)
复制代码
分页查询：
// 前10条const { data, error } = await supabase .from('films') .select(`*, roles(*)`) .range(0, 9)
// 第10条到19条（共10条）const { data, error } = await supabase .from('films') .select(`*, roles(*)`) .range(10, 19)
复制代码
九、Top-Level Filtering
如果你已经在使用 supabase SDK 进行开发了，你可能会碰到一个问题，在使用过滤条件进行关联查询时，会碰到如下返回结果的情况：
const { data, error } = await supabase .from('films') .select(`title, actors(first_name,lat_name)`) .eq('actors.first_name', 'Jehanne')
复制代码
原本只想查询演员的名字为 Jehanne 的电影的信息，但返回的数据长这样：
[ { "title": "Workers Leaving The Lumière Factory In Lyon", "actors": [] }, { "title": "The Dickson Experimental Sound Film", "actors": [] }, { "title": "The Haunted Castle", "actors": [ { "first_name": "Jehanne", "last_name": "d'Alcy" } ] }]
复制代码
前面两条数据是我们不想要的数据，因为他的演员不包含我们期望的演员。我们期望过滤掉这两行数据。此时需要用 inner join 写法：
const { data, error } = await supabase .from('films') .select(`title, actors!inner(first_name,lat_name)`) .eq('actors.first_name', 'Jehanne')
复制代码
还可以使用 null 判断来过滤掉空行数据，下面的写法与 !inner 等效，
GET /films?select=title,actors(*)&actors=not.is.null
复制代码
对应的 supabase 代码如下，可以尝试一下：
const { data, error } = await supabase .from('films') .select(`title, actors!inner(first_name,lat_name)`) .eq('actors.first_name', 'Jehanne') .not('actors', 'is', null)
复制代码
使用关联表进行过滤查询，但是不返回关联表的数据
const { data, error } = await supabase .from('films') .select(`title, actors!inner()`) .eq('actors.first_name', 'Jehanne')
复制代码
上面查询只会返回 films 的 title：
[ { "title": "The Haunted Castle", }]
复制代码
十、平铺展开 JSON 查询结果
上面的所有示例，关联表的查询结果如果是一对一，则以 json对象 格式返回，如果是一对多，则以json数组 格式返回结果。下面是一个例子：
const { data, error } = await supabase .from('films') .select(`title, directors(last_name)`)
复制代码
返回结果：
[ { "title": "Workers Leaving The Lumière Factory In Lyon", "directors": { "last_name": "Lumière" } }, { "title": "The Dickson Experimental Sound Film", "directors": { "last_name": "Dickson" } }]
复制代码
很多开发者可能不习惯这种嵌套式的返回结果，希望每条数据能完整表示表格中的一行，这样的好处是前端在渲染列表时，不需要再对返回结果进行复杂的解析。可以通过如下语法展开结果：
const { data, error } = await supabase .from('films') .select(`title, ...directors(last_name)`)
复制代码
也就是在关联表前面加上三个点...directors ，返回结果如下：
[ { "title": "Workers Leaving The Lumière Factory In Lyon", "last_name": "Lumière" }, { "title": "The Dickson Experimental Sound Film", "last_name": "Dickson" }]
复制代码
为了让返回信息更容易理解，可以使用字段别名：
const { data, error } = await supabase .from('films') .select(`title, ...directors(director_last_name:last_name)`)
复制代码
返回结果如下：
[ { "title": "Workers Leaving The Lumière Factory In Lyon", "director_last_name": "Lumière" }, { "title": "The Dickson Experimental Sound Film", "director_last_name": "Dickson" }]
复制代码
在关联查询的任何关联表前面都可以增加三个点 ... 来展开最终的查询结果。
评论