在PostgreSQL+TypeORM中实现链式层级用户数据查询

针对你的需求，这里提供两种可行的方案来将userTable中的层级数据转换为嵌套children数组的结构：

方案一：应用层构建树形结构 #

先查询所有用户数据，再在代码中通过遍历构建嵌套结构，逻辑简单易维护，适合数据量不大的场景。

1. 定义TypeORM实体 #

确保User实体包含必要字段，并添加用于存储子节点的children属性（该属性不映射到数据库）：

import { Entity, Column, PrimaryGeneratedColumn } from "typeorm";

@Entity("userTable")
export class User {
  @PrimaryGeneratedColumn("uuid")
  id: string;

  @Column()
  name: string;

  @Column({ nullable: true, name: "parentId" })
  parentId: string | null;

  // 用于存储子节点，非数据库字段
  children: User[] = [];
}

2. 查询并构建树形结构 #

通过查询所有用户，建立ID到用户对象的映射，再遍历关联父子节点：

import { getRepository } from "typeorm";
import { User } from "./entities/User";

async function getHierarchicalUsers(): Promise<User[]> {
  // 获取所有用户数据
  const allUsers = await getRepository(User).find();

  // 构建ID到用户的映射，方便快速查找父节点
  const userMap = new Map<string, User>();
  allUsers.forEach(user => {
    userMap.set(user.id, user);
  });

  // 筛选根节点并构建子节点关联
  const rootUsers: User[] = [];
  allUsers.forEach(user => {
    if (!user.parentId) {
      rootUsers.push(user);
    } else {
      const parentUser = userMap.get(user.parentId);
      // 处理可能的无效parentId（脏数据）
      if (parentUser) {
        parentUser.children.push(user);
      }
    }
  });

  return rootUsers;
}

方案二：利用PostgreSQL递归CTE直接生成嵌套结构 #

通过PostgreSQL的递归CTE结合JSON函数，直接在数据库层面生成嵌套结构，适合数据量较大的场景，减少应用层内存消耗。

1. 原生SQL查询方式 #

使用TypeORM的原生查询执行递归CTE：

import { getConnection } from "typeorm";

async function getHierarchicalUsersWithCTE(): Promise<any[]> {
  const recursiveQuery = `
    WITH RECURSIVE user_hierarchy AS (
      -- 基础查询：获取所有根节点（parentId为null）
      SELECT
        id,
        name,
        parentId,
        '[]'::json AS children
      FROM userTable
      WHERE parentId IS NULL

      UNION ALL

      -- 递归查询：关联子节点并构建嵌套children
      SELECT
        child.id,
        child.name,
        child.parentId,
        json_agg(parent_node) AS children
      FROM userTable child
      JOIN user_hierarchy parent_node ON child.parentId = parent_node.id
      GROUP BY child.id, child.name, child.parentId
    )
    -- 将结果聚合为JSON数组
    SELECT json_agg(user_hierarchy) AS tree FROM user_hierarchy;
  `;

  const result = await getConnection().query(recursiveQuery);
  return result[0].tree;
}

2. 注意事项 #

• 若存在循环引用（如子节点的parentId指向自身或后代），递归CTE会触发错误，需提前清理数据或添加循环检测逻辑
• 该方式返回JSON格式数据，若需要映射为User实体，可在查询后手动转换类型

示例输出 #

两种方案最终都会返回类似以下结构的层级数据：

[
  {
    "id": "a1b2c3d4-5678-90ef-ghij-klmnopqrstuv",
    "name": "Root User",
    "parentId": null,
    "children": [
      {
        "id": "wxyz-1234-5678-abcd-efghijklmnop",
        "name": "Child User",
        "parentId": "a1b2c3d4-5678-90ef-ghij-klmnopqrstuv",
        "children": [
          {
            "id": "1234-abcd-5678-wxyz-ijklmnopqrst",
            "name": "Grandchild User",
            "parentId": "wxyz-1234-5678-abcd-efghijklmnop",
            "children": []
          }
        ]
      }
    ]
  }
]

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