小课堂，没有外键关系的两个表进行关联查询

[MemFireDB Robot]

有的时候，我们没有办法在两个表之间建立外键关联，可能是因为历史原因，也可能是技术上不可行（比如FDW表），但业务上又需要在两个表之间进行关联查询，这时候怎么办呢。此时可以通过 function 手动建立关联关系。

1.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 手动构建两个表之间的关联关系时，需要特别注意如下一些约束：

必须使用 SETOF 作为返回结果的定义。如果不使用SETOF，PostgreSQL不会内联这些函数，从而影响性能。
当使用 ROWS 1 来建立 x对一 的关联关系时，要确定function确实只返回一条记录。
关于PostgreSQL的函数内联，可以看一下官方文档的描述。

1.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"
    }
  }
]

1.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;

1.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" }
    ]
  }
]