查询连接和嵌套表

数据 API 会自动检测 Postgres 表之间的关系。因为 Postgres 是关系型数据库，这种情况很常见。

🌐 The data APIs automatically detect relationships between Postgres tables. Since Postgres is a relational database, this is a very common scenario.

一对多连接 #

🌐 One-to-many joins

使用一个存储 orchestral_sections 和 instruments 的示例数据库：

🌐 Use an example database that stores orchestral_sections and instruments:

这些 API 会根据外键自动检测关系：

🌐 The APIs will automatically detect relationships based on the foreign keys:

1
const { data, error } = await supabase.from('orchestral_sections').select(`
2
  id,
3
  name,
4
  instruments ( id, name )
5
`)

1
create table orchestral_sections (
2
  "id" serial primary key,
3
  "name" text
4
);
5
6
create table instruments (
7
  "id" serial primary key,
8
  "name" text,
9
  "section_id" int references "orchestral_sections"
10
);

1
import { QueryData, QueryError, QueryResult } from '@supabase/supabase-js'
2
3
const sectionsWithInstrumentsQuery = supabase.from('orchestral_sections').select(`
4
  id,
5
  name,
6
  instruments (
7
    id,
8
    name
9
  )
10
`)
11
type SectionsWithInstruments = QueryData<typeof sectionsWithInstrumentsQuery>
12
13
const { data, error } = await sectionsWithInstrumentsQuery
14
if (error) throw error
15
const sectionsWithInstruments: SectionsWithInstruments = data

连接类型和连接修饰符 #

🌐 Join types and join modifiers

默认情况下，嵌入关系使用父表的左连接语义：

🌐 By default, embedded relations use left join semantics from the parent table:

• 即使没有相关的行匹配，也会返回父行。
• 当没有匹配时，嵌入关系在一对多连接中是 []，在多对一连接中是 null。

要过滤掉与相关表不匹配的父行，可以在嵌入的关联上使用 !inner。

🌐 To filter out parent rows that do not match the related table, use !inner on the embedded relation.

: 和 !#

🌐 What : and ! mean in join syntax

连接类型的示例数据 #

🌐 Example data for join types

左连接（默认） #

🌐 Left join (default)

这个查询会对一个关联字段（instruments.name）进行过滤，但仍然返回所有父行：

🌐 This query filters on a joined field (instruments.name) but still returns all parent rows:

1
const { data, error } = await supabase
2
  .from('orchestral_sections')
3
  .select(
4
    `
5
    id,
6
    name,
7
    instruments ( id, name )
8
  `
9
  )
10
  .eq('instruments.name', 'flute')

结果 #

🌐 Result

1
[
2
  {
3
    "id": 1,
4
    "name": "strings",
5
    "instruments": []
6
  },
7
  {
8
    "id": 2,
9
    "name": "woodwinds",
10
    "instruments": [{ "id": 3, "name": "flute" }]
11
  },
12
  {
13
    "id": 3,
14
    "name": "percussion",
15
    "instruments": []
16
  }
17
]

内连接 (!inner#

🌐 Inner join (!inner)

添加 !inner 会过滤掉不匹配连接过滤器的父行:

🌐 Adding !inner filters out parent rows that don't match the joined filter:

1
const { data, error } = await supabase
2
  .from('orchestral_sections')
3
  .select(
4
    `
5
    id,
6
    name,
7
    instruments!inner ( id, name )
8
  `
9
  )
10
  .eq('instruments.name', 'flute')

结果 #

🌐 Result

1
[
2
  {
3
    "id": 2,
4
    "name": "woodwinds",
5
    "instruments": [{ "id": 3, "name": "flute" }]
6
  }
7
]

使用连接字段进行过滤 #

🌐 Filtering using joined fields

在过滤器中使用 joined_table.column（例如 eq、neq 和 in）:

🌐 Use joined_table.column in filters (for example eq, neq, and in):

1
const { data, error } = await supabase
2
  .from('instruments')
3
  .select(
4
    `
5
    id,
6
    name,
7
    orchestral_sections!inner ( id, name )
8
  `
9
  )
10
  .eq('orchestral_sections.name', 'woodwinds')

结果 #

🌐 Result

1
[
2
  {
3
    "id": 3,
4
    "name": "flute",
5
    "orchestral_sections": {
6
      "id": 2,
7
      "name": "woodwinds"
8
    }
9
  },
10
  {
11
    "id": 4,
12
    "name": "oboe",
13
    "orchestral_sections": {
14
      "id": 2,
15
      "name": "woodwinds"
16
    }
17
  }
18
]

多对多连接 #

🌐 Many-to-many joins

数据 API 会检测多对多的连接。例如，如果你有一个数据库存储了用户的团队（每个用户可以属于多个团队）:

🌐 The data APIs will detect many-to-many joins. For example, if you have a database which stored teams of users (where each user could belong to many teams):

1
create table users (
2
  "id" serial primary key,
3
  "name" text
4
);
5
6
create table teams (
7
  "id" serial primary key,
8
  "team_name" text
9
);
10
11
create table members (
12
  "user_id" int references users,
13
  "team_id" int references teams,
14
  primary key (user_id, team_id)
15
);

在这些情况下，你不需要显式地定义连接表（members）。如果我们想获取所有的团队以及每个团队的成员：

🌐 In these cases you don't need to explicitly define the joining table (members). If we wanted to fetch all the teams and the members in each team:

1
const { data, error } = await supabase.from('teams').select(`
2
  id,
3
  team_name,
4
  users ( id, name )
5
`)

为有多个外键的连接指定 ON#

🌐 Specifying the ON clause for joins with multiple foreign keys

例如，如果你有一个项目用于跟踪员工上下班打卡时间：

🌐 For example, if you have a project that tracks when employees check in and out of work shifts:

1
-- Employees
2
create table users (
3
  "id" serial primary key,
4
  "name" text
5
);
6
7
-- Badge scans
8
create table scans (
9
  "id" serial primary key,
10
  "user_id" int references users,
11
  "badge_scan_time" timestamp
12
);
13
14
-- Work shifts
15
create table shifts (
16
  "id" serial primary key,
17
  "user_id" int references users,
18
  "scan_id_start" int references scans, -- clocking in
19
  "scan_id_end" int references scans, -- clocking out
20
  "attendance_status" text
21
);

在这种情况下，你需要明确指定连接，因为 shifts 上的连接列有歧义，两个列都引用了 scans 表。

🌐 In this case, you need to explicitly define the join because the joining column on shifts is ambiguous as they are both referencing the scans table.

要获取与特定 scan 相关的所有带有 scan_id_start 和 scan_id_end 的 shifts，可以使用以下语法：

🌐 To fetch all the shifts with scan_id_start and scan_id_end related to a specific scan, use the following syntax:

1
const { data, error } = await supabase.from('shifts').select(
2
  `
3
    *,
4
    start_scan:scans!scan_id_start (
5
      id,
6
      user_id,
7
      badge_scan_time
8
    ),
9
   end_scan:scans!scan_id_end (
10
     id,
11
     user_id,
12
     badge_scan_time
13
    )
14
  `
15
)