如何在SurrealDB中指定跳数递归查询树形结构子树？

要实现递归遍历树形结构并生成嵌套格式的结果，你可以使用SurrealDB的**递归CTE（WITH RECURSIVE）**语法，同时通过深度限制来指定跳数。以下是具体的实现方案：

1. 查询完整子树（无跳数限制） #

这个查询会从根节点开始，递归遍历所有层级的子节点，并组装成你需要的嵌套结构：

WITH RECURSIVE subtree(id, title, children, depth) AS (
    -- 初始节点：根节点，深度设为0
    SELECT 
        id, 
        title, 
        [] AS children, 
        0 AS depth
    FROM node:root

    UNION ALL

    -- 递归遍历子节点，深度递增
    SELECT
        child.id,
        child.title,
        [] AS children,
        parent.depth + 1 AS depth
    FROM subtree parent
    INNER JOIN parent->is_parent->node child
)
-- 从根节点开始组装嵌套结构
SELECT 
    title,
    array(
        SELECT 
            title,
            children
        FROM subtree child
        WHERE child.depth = parent.depth + 1
        AND EXISTS (SELECT * FROM parent->is_parent->node WHERE id = child.id)
    ) AS children
FROM subtree parent
WHERE depth = 0;

执行后会返回如下嵌套结果：

{
  "title": "Root",
  "children": [
    {
      "title": "Child 1",
      "children": [
        {
          "title": "GrandChild 1.1",
          "children": []
        }
      ]
    },
    {
      "title": "Child 2",
      "children": []
    },
    {
      "title": "Child 3",
      "children": []
    }
  ]
}

2. 指定跳数限制 #

如果需要限制遍历的层级（比如只获取根节点+1级子节点，或者根节点+2级子节点），只需在递归部分添加深度判断条件：

示例：限制最多1跳（仅根节点和一级子节点） #

WITH RECURSIVE subtree(id, title, children, depth) AS (
    SELECT 
        id, 
        title, 
        [] AS children, 
        0 AS depth
    FROM node:root

    UNION ALL

    SELECT
        child.id,
        child.title,
        [] AS children,
        parent.depth + 1 AS depth
    FROM subtree parent
    INNER JOIN parent->is_parent->node child
    WHERE parent.depth < 1 -- 限制深度不超过1，即只遍历到一级子节点
)
SELECT 
    title,
    array(
        SELECT 
            title,
            children
        FROM subtree child
        WHERE child.depth = parent.depth + 1
        AND EXISTS (SELECT * FROM parent->is_parent->node WHERE id = child.id)
    ) AS children
FROM subtree parent
WHERE depth = 0;

示例：限制最多2跳（根节点+一级+二级子节点） #

只需把WHERE parent.depth < 1改成WHERE parent.depth < 2即可。

3. 优化：移除空children数组 #

如果不想返回空的children字段，可以用CASE语句判断数组长度，只在有子节点时返回该字段：

WITH RECURSIVE subtree(id, title, depth) AS (
    SELECT 
        id, 
        title, 
        0 AS depth
    FROM node:root

    UNION ALL

    SELECT
        child.id,
        child.title,
        parent.depth + 1 AS depth
    FROM subtree parent
    INNER JOIN parent->is_parent->node child
)
SELECT 
    title,
    CASE
        WHEN array_length(child_nodes) > 0 THEN child_nodes
        ELSE NULL
    END AS children
FROM (
    SELECT 
        parent.title,
        array(
            SELECT 
                child.title,
                CASE
                    WHEN array_length(grandchild_nodes) > 0 THEN grandchild_nodes
                    ELSE NULL
                END AS children
            FROM (
                SELECT 
                    child.id,
                    child.title,
                    array(SELECT title FROM subtree grandchild WHERE grandchild.depth = child.depth + 1 AND EXISTS (SELECT * FROM child->is_parent->node WHERE id = grandchild.id)) AS grandchild_nodes
                FROM subtree child
                WHERE child.depth = parent.depth + 1 AND EXISTS (SELECT * FROM parent->is_parent->node WHERE id = child.id)
            )
        ) AS child_nodes
    FROM subtree parent
    WHERE depth = 0
);

执行后孙节点的children字段会被省略，更贴近你给出的示例格式。

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