Supabase与Dart中多对多关联查询报错问题求助

问题场景 #

按照Supabase多对多关联逻辑创建了三张表：

create table parties (
  id uuid primary key,
  name text not null
);

create table users (
  id uuid not null references auth.users on delete cascade,
  phone_number text,

  primary key (id)
);

create table parties_members (
  party_id uuid not null references parties on delete cascade,
  user_id uuid not null references users on delete cascade
);

alter table parties_members add primary key (party_id, user_id);

使用supabase_flutter: ^1.8.1编写查询代码时，主表关联中间表的查询能正常返回数据，但直接通过中间表关联parties和users的语句抛出错误：Could not find a relationship between 'parties' and 'users' in the schema cache, code: PGRST200。

相关Dart代码：

try {
  // 正常返回的查询
  var parties = await supabase.from("parties").select('''
    id,
    parties_members(*)
  ''');

  var users = await supabase.from("users").select('''
    id,
    parties_members(*)
  ''');

  log("parties with join table: $parties");
  log("users with join table: $users");

  // 报错的查询
  var partiesWithUsers = await supabase.from("parties").select('''
    id,
    users(*)
  ''');
} catch (e, stacktrace) {
  log("Error fetching parties", error: e, stackTrace: stacktrace);
}

正常返回的日志示例：

[log] parties with join table: [{id: b6e10467-7d1f-4705-8bb5-b59484c86955, parties_members: [{party_id: b6e10467-7d1f-4705-8bb5-b59484c86955, user_id: 48d4a132-46f1-439a-94b3-3ecb49d98a49}]}]
[log] users with join table: [{id: 48d4a132-46f1-439a-94b3-3ecb49d98a49, parties_members: {party_id: b6e10467-7d1f-4705-8bb5-b59484c86955, user_id: 48d4a132-46f1-439a-94b3-3ecb49d98a49}}]

问题原因与解决 #

验证后确认代码本身无错误，后续问题自动恢复，推测是Supabase服务端的Schema缓存延迟导致的。PostgREST服务依赖Schema缓存识别表间关联关系，新创建多对多中间表后，缓存需要时间更新，期间无法识别parties和users的间接关联。

遇到类似问题可尝试：

• 等待5-10分钟后重新执行查询
• 在Supabase控制台SQL编辑器中执行以下语句手动触发Schema重载：

  NOTIFY pgrst, 'reload schema';
  

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