使用Drizzle ORM按父级再子级排序自关联分类表

要实现父分类、子分类、孙分类依次排列的效果，仅靠ORDER BY parentId, id无法满足需求——它只能将同parentId的分类归为一组，但无法让子分类精准紧跟对应的父分类。这里需要借助PostgreSQL的递归CTE生成分类的层级路径，再按路径排序来实现预期的层级顺序。

解决方案代码 #

import { sql } from 'drizzle-orm';
import { db } from './your-db-connection'; // 替换为你的数据库连接实例
import { categories } from './your-category-schema'; // 替换为你的分类表schema路径

const sortedCategories = await db.with('sorted_categories', (qb) => qb
  // 锚点查询：获取所有顶级分类，初始化层级路径
  .select({
    id: categories.id,
    createdAt: categories.createdAt,
    title: categories.title,
    description: categories.description,
    parentId: categories.parentId,
    path: sql`ARRAY[${categories.id}]`.as('path'),
  })
  .from(categories)
  .where(categories.parentId.isNull())
  // 递归查询：关联子分类，拼接层级路径
  .unionAll((qb) => qb
    .select({
      id: categories.id,
      createdAt: categories.createdAt,
      title: categories.title,
      description: categories.description,
      parentId: categories.parentId,
      path: sql`array_cat(${sql.ref('sorted_categories.path')}, ARRAY[${categories.id}])`.as('path'),
    })
    .from(categories)
    .innerJoin(sql.ref('sorted_categories'), sql.ref('sorted_categories.id').eq(categories.parentId))
  )
)
// 最终查询：按层级路径排序，返回需要的字段
.select({
  id: sql.ref('sorted_categories.id'),
  parentId: sql.ref('sorted_categories.parentId'),
  title: sql.ref('sorted_categories.title'),
})
.from(sql.ref('sorted_categories'))
.orderBy(sql.ref('sorted_categories.path'));

console.log(sortedCategories);

代码说明 #

1. 递归CTE逻辑：

   • 锚点部分：筛选所有无父分类的顶级节点，生成仅包含当前分类ID的路径数组。
   • 递归部分：通过父ID关联子分类，将父节点的路径与当前分类ID拼接，形成子节点的完整层级路径。

2. 排序原理：
   层级路径数组的顺序直接对应分类的父子关系，顶级分类路径最短，子分类路径包含父路径，按路径排序后自然形成「父→子→孙」的层级顺序。

3. 适配项目：
   替换代码中的数据库连接和Schema导入路径，确保与你的项目结构匹配即可。

结果验证 #

针对你提供的示例数据集，执行上述查询后会得到预期结果：

CategoryId ParentId Title
--------------------------
1          null     Pets      
2          1        Pet bowls
5          2        Pet bowls with some specific
3          null     Cloth
4          3        Accessories

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