Ecto CTE Recursively preload children

Questions / Help
, ,
1

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.

2 Likes
2

:slight_smile:

I guess this functionality is a little less used than I thought. I’ll find some more time to dig in and see if I can get a solution to post here for others to reference later.

1 Like
3

Perhaps https://github.com/coryodaniel/arbor could be of some use?

4 Likes
4

Arbor seems to still use a list like structure. Is there a more idiomatic way to achieve this? (Conscious of the fact that this can be done using a simple recursion, just wondering if Ecto can do it out of the box.)

5

I didn’t test yet, but maybe this is what you are looking for:

Self-reference many-to-many

2 Likes
6

CTE is part of the Postgres’ feature, and is intended to return a flat list.

As for nesting, I think the first step here is to think about how deep you want the query to go, having infinite depth could cause unexpected problem down the road.

That being said, we can use preload to get what we need. I did this for my company’s use case:

def tree_traversal(query, :nested} do
    preload =
      Enum.reduce(1..@default_traverse_layer, :descendants, fn _, acc ->
        [descendants: acc]
      end)

    preload(query, ^preload)
end

Record
|> tree_traversal(params.include_descendants) # we allow for :flat or :nested
2 Likes