PostgreSQL树结构嵌套排序及节点前后项查询方案咨询

需求背景 #

我有一个CourseContentEntity表，包含id（字符串）、parentId（字符串或null）和order（数字）字段，数据呈树状结构：相同parentId的行属于同一节点的子节点，order用于对每个父节点的子节点排序（不同父节点的子节点order可重复）。

这个树结构代表课程内容，我需要实现导航功能：根据给定节点的id，获取它在**DFS嵌套排序（先按parentId+order排父节点，再递归插入按order排序的子节点）**规则下的上一行和下一行数据。

现有问题 #

最初的查询只按parentId和order排序，无法实现递归嵌套子节点的效果，结果不符合预期：

SELECT "content"."id", "content"."parentId", "content"."order" 
FROM "course_content_entity" "content" 
WHERE ( "content"."deletedAt" IS NULL ) AND ("content"."courseId" = '05dd28d5-a244-4ca1-b7fb-fc3bc7b2e422') 
order by "content"."parentId", "content"."order" 

方案一：递归CTE实现嵌套排序 #

参考他人查询修改后，我写出了能实现嵌套排序的递归CTE查询，它会生成position_array（记录节点在DFS路径中的位置）和dfs_position（全局DFS顺序的行号），可以正确按嵌套结构排序：

WITH RECURSIVE node(id, "parentId", "order", "name", position_array) AS (
    SELECT
        root.id,
        root."parentId",
        root.order,
        root.name,
        ARRAY[ROW_NUMBER() OVER (ORDER BY root.order, root.id)] AS position_array
    FROM course_content_entity root
    WHERE root."parentId" IS NULL AND root."courseId" = '18f75c26-3608-49c2-8e1c-56618b35c780'
UNION ALL
    SELECT
        child.id,
        child."parentId",
        child.order,
        child.name,
        parent.position_array || ROW_NUMBER() OVER (PARTITION BY child."parentId" ORDER BY child.order) AS position_array
    FROM course_content_entity child
    INNER JOIN node parent  ON parent.id::uuid = child."parentId"::uuid
    WHERE child."courseId" = '18f75c26-3608-49c2-8e1c-56618b35c780'
)
SELECT
    n.id,
    n."parentId",
    n.order,
    n.name,
    n.position_array,
    ROW_NUMBER() OVER (ORDER BY n.position_array) AS dfs_position
FROM node n
ORDER BY dfs_position;

目前这个查询能正确生成嵌套排序的结果，但还需要扩展来获取指定节点的前后行，并且我希望用TypeORM的queryBuilder来实现它。

方案二：当前TypeORM代码实现 #

我已经用TypeORM实现了一个getPagination方法，通过多次查询来处理前后节点的逻辑：

async getPagination(contentId: string) {
  const content = await this._courseContentRepository.findOne({
    where: { id: contentId },
    relations: { course: true },
  });
  const query = this._courseContentRepository
    .createQueryBuilder('content')
    .leftJoin('content.course', 'course')
    .andWhere('course.id = :courseId', { courseId: content.course.id });

  /* NEXT */
  const getNext = async (content: CourseContentEntity) => {
    const nextQuery = query.clone();
    if (content.parentId) {
      nextQuery.where('content.parentId = :parentId', { parentId: content.parentId });
    } else {
      nextQuery.where('content.parentId IS NULL');
    }
    return await nextQuery
      .andWhere('content.order > :order', { order: content.order })
      .orderBy('content.order', 'ASC')
      .getOne();
  };

  const firstChild = await this._courseContentRepository.findOne({
    where: { parentId: content.id },
  });
  let next = firstChild;

  // Try to get next sibling
  if (!next) next = await getNext(content);
  if (!next && content.parentId) {
    // It's a last child node -> take node after parent node
    const parent = await this._courseContentRepository.findOne({
      where: { id: content.parentId },
    });

    next = await getNext(parent);
  }

  /* PREV */
  const getPrev = async (parentId: string | null, order: number) => {
    const prevQuery = query.clone();
    if (parentId) {
      prevQuery.where('content.parentId = :parentId', { parentId: parentId });
    } else {
      prevQuery.where('content.parentId IS NULL');
    }
    return await prevQuery.andWhere('content.order < :order', { order }).orderBy('content.order', 'DESC').getOne();
  };

  let prev = await getPrev(content.parentId, content.order);
  if (prev) {
    const lastChild = await query
      .andWhere('content.parentId = :parentId', { parentId: prev.id })
      .orderBy('content.order', 'DESC')
      .getOne();
    if (lastChild) prev = lastChild;
  } else {
    if (content.parentId) {
      const parent = await this._courseContentRepository.findOne({
        where: { id: content.parentId },
      });
      prev = parent;
    } else {
      prev = await getPrev(null, content.order);
      if (prev) {
        const lastChild = await query
          .andWhere('content.parentId = :parentId', { parentId: prev.id })
          .orderBy('content.order', 'DESC')
          .getOne();
        if (lastChild) prev = lastChild;
      }
    }
  }

  return {
    next: next
      ? {
          id: next.id,
          name: next.name,
        }
      : undefined,
    prev: prev
      ? {
          id: prev.id,
          name: prev.name,
        }
      : undefined,
  };
}

逻辑说明：

• 下节点：优先找当前节点的第一个子节点；没有则找同层级的下一个兄弟节点；如果是父节点的最后一个子节点，则找父节点的下一个兄弟节点。
• 上节点：优先找同层级的上一个兄弟节点，再取该兄弟的最后一个子节点；没有则找父节点；如果是根节点则找前一个根节点的最后子节点。

纠结点 #

现在我不确定应该选择哪种方案：是继续完善当前的TypeORM代码实现，还是基于递归CTE的方案扩展出前后节点查询？

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