Elixir Ecto如何递归获取父节点下所有层级子节点

针对LabTest.Schema的树形结构数据，要获取名为Microbiology根节点下所有层级子节点名称，可根据数据量规模选择以下两种实现方案：

方案1：递归CTE实现（推荐） #

生产环境、大数据量、深层级场景优先选这个方案
PostgreSQL、MySQL 8.0+等主流数据库都支持递归CTE语法，递归逻辑全在数据库层执行，不需要把全表数据加载到应用内存，性能优势明显。Ecto 3.0+原生支持递归CTE构造，代码实现如下：

defmodule LabTest do
  use Ecto.Schema
  import Ecto.Query

  schema "lab_tests" do
    field :name, :string
    field :parent_id, Ecto.UUID
  end

  @doc "返回Microbiology节点下所有层级子节点的名称列表"
  def all_microbiology_child_names do
    # 递归CTE逻辑定义
    recursive_query =
      # 锚点段：定位目标根节点
      from lt in __MODULE__,
        where: lt.name == "Microbiology" and is_nil(lt.parent_id),
        select: %{id: lt.id, parent_id: lt.parent_id, name: lt.name, level: 0}

      union_all:
        # 递归段：逐层关联下一级子节点
        from lt in __MODULE__,
          join: parent in "recursive_tree",
          on: lt.parent_id == parent.id,
          select: %{id: lt.id, parent_id: lt.parent_id, name: lt.name, level: parent.level + 1}

    # 从递归结果中排除根节点本身，只返回子节点名称
    from(node in recursive_ctes(cte: {"recursive_tree", recursive_query}),
      where: node.level > 0,
      select: node.name
    )
    |> Repo.all()
  end
end

• 记得给parent_id字段加数据库索引，能大幅提升递归关联的查询速度，索引可以在migration中添加：

create index(:lab_tests, [:parent_id])

• 如果需要返回节点层级、完整节点路径等信息，直接修改select部分的返回字段即可。

方案2：内存递归遍历 #

适合千条级以内小数据量场景
如果lab_tests表总数据量很小，可以先把需要的字段全量加载到应用内存，再通过递归遍历收集子节点，代码逻辑更直观易调试，不需要依赖数据库的CTE特性：

def all_microbiology_child_names_in_memory do
  # 仅加载需要的三个字段，尽可能降低内存占用
  all_nodes =
    from(lt in LabTest, select: %{id: lt.id, parent_id: lt.parent_id, name: lt.name})
    |> Repo.all()

  # 定位目标根节点
  root_node = Enum.find(all_nodes, &(&1.name == "Microbiology" and is_nil(&1.parent_id)))

  # 递归收集所有层级子节点名称
  child_collector = fn collector, parent_id ->
    parent_id
    # 找到当前节点的所有直接子节点
    |> then(fn pid -> Enum.filter(all_nodes, &(&1.parent_id == pid)) end)
    # 递归收集每个子节点的下级节点，汇总名称
    |> Enum.flat_map(fn child ->
      [child.name | collector.(collector, child.id)]
    end)
  end

  child_collector.(child_collector, root_node.id)
end

• 表数据量超过1万条时不要用这个方案，全表加载内存占用高，遍历速度会明显变慢。

如果后续有大量树形结构操作需求（比如查询祖先节点、移动节点位置、判断节点从属关系等），可以直接使用封装好的树形结构Ecto扩展库，不需要重复实现递归逻辑。

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