Recursively Preloading Children from Join Table w/ Elixir & Ecto

graph_preload.ex

# Use a recursive CTE to turn the join table into a (partial) closure table,
# then join all of the child records for the provided parent(s)
query = """
WITH RECURSIVE traversal(depth, parent_id, child_id) AS (
    SELECT
      1 depth,
      parent_id,
      child_id
    FROM rules_rules
    WHERE parent_id = ANY($1)
  UNION
    SELECT
      t.depth + 1,
      r.parent_id,
      r.child_id
    FROM traversal t
    JOIN rules_rules r ON r.parent_id = t.child_id
)
SELECT
  t.parent_id,
  r.*
FROM traversal t
JOIN rules r ON t.child_id = r.id
ORDER BY depth DESC
"""

def preload_descendants(ids) do
  with encoded <- Enum.map(ids, &Ecto.UUID.dump!/1),
       {:ok, result} <- Ecto.Adapters.SQL.query(Repo, query, [encoded]),
       [ _ | columns] <- result.columns
  do
    graph = result.rows
      |> Enum.map(&instantiate_rules(columns, &1))
      |> Enum.reduce(%{}, &nest_descendants/2)

    for id <- ids, child <- graph[id], do: {id, child}
  end
end

defp instantiate_rules(columns, [parent_id | attrs]) do
  parent_id = Ecto.UUID.cast!(parent_id)
  rule = Repo.load(Rule, {columns, attrs})
  {parent_id, rule}
end

defp nest_descendants({parent_id, %{id: child_id} = child}, acc) do
  case acc do
    %{^parent_id => siblings, ^child_id => children} ->
      Map.put(acc, parent_id, [%Rule{child | children: children} | siblings])
    %{^child_id => children} ->
      Map.put(acc, parent_id, [%Rule{child | children: children}])
    %{^parent_id => siblings} ->
      Map.put(acc, parent_id, [child | siblings])
    %{} ->
      Map.put(acc, parent_id, [child])
  end
end

###

Repo.get(Rule, "2bbe6138-d81c-4076-be2b-1339834723b4") |> Repo.preload(children: &preload_descendants/1)