Hi everyone. I’m trying to wrap my head around using the CTE functionality in Ecto and I haven’t quite been able to figure it out by looking at the documentation. I’m working on building a simple nested comment system to try it out.

defmodule Comment do
  schema "comments" do
    field :body, :string

    has_many :children, {"comments", __MODULE__}, foreign_key: :parent_id

    belongs_to :parent, __MODULE__

    timestamps()
  end

end

I’d like to be able to get all the childen comments for a given comment, and all their children, and so on.

Looking at the Ecto docs, https://hexdocs.pm/ecto/Ecto.Query.html#with_cte/3-recursive-ctes, I’ve come up with this:

comment_tree_initial_query = Comment |> where([c], is_nil(c.parent_id))

comment_tree_recursion_query = Comment |> join(:inner, [c], ct in "comment_tree", on: c.parent_id == ct.id)

comment_tree_query = comment_tree_initial_query |> union_all(^comment_tree_recursion_query)

Comment |> recursive_ctes(true) |> with_cte("comment_tree", as: ^comment_tree_query)

Which seems to be in the right direction, but this just gives me back all comments in a flat list, vs them being loaded into children fields. I must be missing something simple, but I cannot quite figure out what.