"Uncaught Error: More than one relationship was found"与 Supabase 客户端查询

Question

我正在构建一个多租户应用程序，并在添加指向同一表的多个关系后遇到错误:

Uncaught Error: More than one relationship was found for teams and users

执行查询时:

const data = await supabaseClient.from('organizations')
.select(`
*,
teams(
id,
org_id,
name,
members:users(
id,
full_name,
avatar_url
)
)
`);

我有以下表结构(为了简洁，省略了一些字段):

table users (
id uuid PK
full_name text
email text
)
table organizations (
id uuid PK
....
)
table organization_memberships (
id uuid PK
organization_id uuid FK
user_id uuid FK
role ENUM
)
table teams (
id uuid PK
name text PK
)
table team_memberships (
id uuid PK
team_id uuid FK
user_id uuid FK
role ENUM
)
table team_boards (
id uuid PK
team_id uuid FK
owner_id uuid FK
)

在底层，Supabase使用postest进行查询。我已经从错误消息中破译了查询是含糊不清的，并且不确定要实现哪个关系。我不知道如何告诉Supabase在这个特定的查询中使用哪个关系来避免这个错误。

下面是来自postest的更详细的控制台错误:

{
hint: "By following the 'details' key, disambiguate the request by changing the url to /origin?select=relationship(*) or /origin?select=target!relationship(*)",
message: 'More than one relationship was found for teams and users',
details: [
{
origin: 'public.teams',
relationship: 'public.team_memberships[team_memberships_team_id_fkey][team_memberships_user_id_fkey]',
cardinality: 'm2m',
target: 'public.users'
},
{
origin: 'public.teams',
relationship: 'public.team_boards[team_boards_team_id_fkey][team_boards_owner_id_fkey]',
cardinality: 'm2m',
target: 'public.users'
}
]
}

Answer 1

深入挖掘PostgREST文档，结果发现我要找的是消歧运算符!。

工作查询看起来像这样(注意，我们消除了使用哪个关系来满足members查询的歧义):

const data = await supabaseClient.from('organizations')
.select(`
*,
teams(
id,
org_id,
name,
members:users!team_memberships(
id,
full_name,
avatar_url
)
)
`);