在Supabase中通过JOIN实现孙级关联：查询teams表并返回products字段

数据库模式 #

ER关系图 #

建表SQL #

CREATE TABLE organization (
   org_name text  NOT NULL,
   PRIMARY KEY (org_name)
);

CREATE TABLE teams (
   org_name text NOT NULL,
   team_name text NOT NULL,
   PRIMARY KEY (org_name, team_name),
   FOREIGN KEY (org_name)
      REFERENCES organization (org_name)
);

CREATE TABLE projects (
   org_name text NOT NULL,
   team_name text NOT NULL,
   project_name text NOT NULL,
   products jsonb,
   PRIMARY KEY (org_name, team_name, project_name),
   FOREIGN KEY (org_name)
      REFERENCES organization (org_name),
   FOREIGN KEY (org_name, team_name)
      REFERENCES teams (org_name, team_name)
);

用户需求 #

我需要查询teams表的同时，返回对应projects表中的products字段，请问是否有可行的实现方式？

可行实现方式 #

当然可以实现，根据不同的业务需求，有以下几种实用写法：

1. 基础关联查询 #

如果需要保留所有团队记录（包括无对应项目的团队），用LEFT JOIN；如果只需要有对应项目的团队，换成INNER JOIN即可：

SELECT 
    t.org_name,
    t.team_name,
    p.products
FROM teams t
LEFT JOIN projects p 
    ON t.org_name = p.org_name 
    AND t.team_name = p.team_name;

2. 聚合合并同一团队的所有Products #

如果一个团队对应多个项目，想要把所有项目的products合并成一个JSON数组，使用PostgreSQL的jsonb_agg()函数：

SELECT 
    t.org_name,
    t.team_name,
    jsonb_agg(p.products) AS all_products
FROM teams t
LEFT JOIN projects p 
    ON t.org_name = p.org_name 
    AND t.team_name = p.team_name
GROUP BY t.org_name, t.team_name;

如果要过滤掉无项目的团队，可在末尾添加HAVING jsonb_agg(p.products) != '[]'::jsonb;

3. 关联特定项目的Products #

如果只需要某个指定项目的products，直接在关联条件里添加项目名称过滤：

SELECT 
    t.org_name,
    t.team_name,
    p.products
FROM teams t
LEFT JOIN projects p 
    ON t.org_name = p.org_name 
    AND t.team_name = p.team_name
    AND p.project_name = '目标项目名称';

内容的提问来源于stack exchange，提问作者Mansueli