Prisma自关联查询：如何按父级分组并按标题排序集合

完全可以实现，下面提供两种可行方案：

方案一：利用Postgres递归CTE直接查询层级结构 #

Postgres支持递归公共表表达式（CTE），可直接在数据库层面获取按标题排序的层级数据，同时保留层级关系。

编写递归CTE查询 #

WITH RECURSIVE collection_hierarchy AS (
  -- 基础查询：获取所有顶级集合（parentId为null），初始化层级路径和深度
  SELECT 
    id, 
    title, 
    parentId, 
    ARRAY[title] AS path,
    1 AS depth
  FROM "Collection"
  WHERE "parentId" IS NULL
  UNION ALL
  -- 递归查询：关联子集合，拼接路径并增加深度
  SELECT 
    c.id, 
    c.title, 
    c."parentId", 
    ch.path || c.title,
    ch.depth + 1
  FROM "Collection" c
  JOIN collection_hierarchy ch ON c."parentId" = ch.id
)
-- 按路径排序，确保同级和子级都按标题排序
SELECT id, title, parentId, depth FROM collection_hierarchy ORDER BY path;

在Prisma中执行原生查询 #

通过Prisma的$queryRaw方法执行上述SQL：

import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

async function getHierarchicalCollections() {
  const result = await prisma.$queryRaw`
    WITH RECURSIVE collection_hierarchy AS (
      SELECT 
        id, 
        title, 
        "parentId", 
        ARRAY[title] AS path,
        1 AS depth
      FROM "Collection"
      WHERE "parentId" IS NULL
      UNION ALL
      SELECT 
        c.id, 
        c.title, 
        c."parentId", 
        ch.path || c.title,
        ch.depth + 1
      FROM "Collection" c
      JOIN collection_hierarchy ch ON c."parentId" = ch.id
    )
    SELECT id, title, "parentId", depth FROM collection_hierarchy ORDER BY path;
  `;
  
  // 可根据depth字段格式化出缩进的层级列表
  return result;
}

方案二：Prisma查询全量数据后在应用层构建层级结构 #

若不想写原生SQL，可先通过Prisma获取所有集合数据，再在应用层构建树形结构并排序。

1. 查询所有集合并按标题排序 #

async function getAllCollections() {
  return await prisma.collection.findMany({
    orderBy: { title: 'asc' },
    select: { id: true, title: true, parentId: true }
  });
}

2. 构建层级结构并保持排序 #

function buildHierarchy(collections: Array<{ id: string; title: string; parentId: string | null }>) {
  const map = new Map<string, typeof collections>();
  const rootNodes: typeof collections = [];

  // 初始化Map，每个节点对应一个空数组
  collections.forEach(col => map.set(col.id, []));

  // 遍历所有集合，将子节点归类到父节点下
  collections.forEach(col => {
    if (col.parentId) {
      map.get(col.parentId)?.push(col);
    } else {
      rootNodes.push(col);
    }
  });

  // 递归为每个节点添加children，并保持子节点按标题排序
  function addChildren(node: typeof collections[0]) {
    const children = map.get(node.id) || [];
    children.sort((a, b) => a.title.localeCompare(b.title));
    node.children = children.map(child => addChildren(child));
    return node;
  }

  // 根节点按标题排序后添加子节点
  rootNodes.sort((a, b) => a.title.localeCompare(b.title));
  return rootNodes.map(node => addChildren(node));
}

3. 格式化输出层级列表 #

拿到层级结构后，可根据嵌套深度生成带缩进的列表：

function formatHierarchyList(nodes: Array<{ title: string; children?: typeof nodes }>, depth = 0) {
  let output = '';
  const indent = '  '.repeat(depth);
  nodes.forEach(node => {
    output += `${indent}- ${node.title}\n`;
    if (node.children && node.children.length > 0) {
      output += formatHierarchyList(node.children, depth + 1);
    }
  });
  return output;
}

调用示例 #

async function main() {
  const collections = await getAllCollections();
  const hierarchy = buildHierarchy(collections);
  const formattedList = formatHierarchyList(hierarchy);
  console.log(formattedList);
}

main()
  .catch(e => console.error(e))
  .finally(async () => await prisma.$disconnect());

两种方案各有优劣：方案一在数据库层面完成层级计算和排序，适合数据量较大的场景；方案二更灵活，无需编写原生SQL，适合数据量较小或需要在应用层做更多自定义处理的场景。

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