如何合并PostgreSQL查询中多角色用户对应的多条返回行

数据库设计评估 #

你当前的三表（用户表、角色表、用户角色关联表）多对多设计完全符合第三范式，没有冗余问题，不需要调整，不要额外加冗余的role_list数组字段，后续维护数据一致性的成本会非常高。

解决方案 #

方案1：PostgreSQL查询层面直接聚合（更推荐） #

PostgreSQL自带数组聚合函数array_agg()，可以直接在查询时把同用户的角色合并成数组，不需要额外在业务层处理，性能也更高。修改后的SQL如下：

SELECT 
  users.id AS uid,
  users.first_name,
  array_agg(roles.name) AS role_list
FROM users
INNER JOIN user_role ON user_role.user_id = users.id
INNER JOIN roles ON roles.id = user_role.role_id
GROUP BY users.id, users.first_name
ORDER BY users.id;

直接执行这个查询返回的结果就是你需要的结构，不需要额外处理。如果需要对角色排序，也可以在array_agg里加排序规则：array_agg(roles.name ORDER BY roles.id) AS role_list。

方案2：Node.js端快速格式化（适合不想改SQL的场景） #

不需要写复杂的循环判断，用Map做临时存储，几行代码就能搞定，时间复杂度仅为O(n)：

function formatUserRole(rows) {
  const userMap = new Map()
  rows.forEach(row => {
    if (!userMap.has(row.uid)) {
      userMap.set(row.uid, {
        uid: row.uid,
        first_name: row.first_name,
        role_list: []
      })
    }
    userMap.get(row.uid).role_list.push(row.name)
  })
  return Array.from(userMap.values())
}

// 调用示例：把当前查询结果传入即可得到期望输出
const result = formatUserRole(currentQueryOutput)

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