如何通过Supabase select()在关联表中获取属性而非记录数组？

当前查询代码 #

const { data } = await supabase.from('teams').select(`
  *,
  members:profiles (
    *,
    roles (
      name
    ),
    members(
      isTeamLeader
    )
  )
`)

当前响应结构 #

{
    "id": "team_id",
    "created_at": "timestamp",
    "name": "team_name",
    "members": [
        {
            "first_name": "profile_first_name",
            "last_name": "profile_last_name",
            "user_id": "user_id",
            "created_at": "timestamp",
            "id": "profile_id",
            "avatar_id": null,
            "bio": "profile_bio",
            "roles": [
                {
                    "name": "role_name"
                }
            ],
            "members": [
                {
                    "isTeamLeader": true
                }
            ]
        }
    ]
}

期望响应结构 #

{
  "id": "team_id",
  "created_at": "timestamp",
  "name": "team_name",
  "members": [
      {
          "first_name": "profile_first_name",
          "last_name": "profile_last_name",
          "user_id": "user_id",
          "created_at": "timestamp",
          "id": "profile_id",
          "avatar_id": null,
          "bio": "profile_bio",
          "role_name": "role_name",
          "isTeamLeader": true
      }
  ]
}

问题 #

请问应如何修改请求代码以实现该结构？

直接利用Supabase的列别名和嵌套查询扁平化能力，将关联表的字段提取为当前层级字段，修改后的代码如下：

const { data } = await supabase.from('teams').select(`
  *,
  members:profiles (
    *,
    role_name:roles(name),
    isTeamLeader:members(isTeamLeader)
  )
`)

关键修改说明 #

1. role_name:roles(name)：把roles关联表的name字段重命名为role_name，直接嵌入到profiles层级，替代原有的数组结构。
2. isTeamLeader:members(isTeamLeader)：将members关联表的isTeamLeader字段提取为当前层级的同名字段，去掉嵌套数组。

注：如果roles或members关联表可能返回多条数据，Supabase会自动取第一条记录的字段值。若你的业务逻辑中每个用户对应唯一角色和团队成员记录，这个写法完全适用。

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