peck

peck

Ecto CTE Recursively preload children

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.

Most Liked

seanwash

seanwash

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

dhony

dhony

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

Self-reference many-to-many

yuchunc

yuchunc

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
peck

peck

: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.

Where Next?

Popular in Questions Top

openscript
Hello! Sorry for this astonishing simple question, but I’m really stuck. I try to set up the intellij-elixir plugin, but I don’t know ho...
New
jerry
Good day to you all. I have been struggling to get a query involving like and ilike to work. Can anyone assist me on this, please? pro...
New
polypush135
As many of you may have realized by now (sorry for all the posts here) I’ve been working on a db problem where I’m trying to aggregate a ...
New
freewebwithme
Using vs code and installed ElixirLS: support and debugger. And I got an error popped up on start up says Failed to run ‘elixir’ comma...
New
qwerescape
Is there a way to get the call stack or stack trace at any point in the code? Not from exceptions, but an expression that returns how the...
New
fayddelight
I tried installing elixir 1.11.2 erlang 23.3.4 via asdf in my zsh shell. Enabled the versions locally and globally. When I list them ...
New
shahryarjb
Hello, I have map which I want to convert it to string like this: the map: %{last_name: "tavakkoli", name: "shahryar"} the string I ne...
New
fireproofsocks
Forgive me if this is obvious, but how does one delete a database record WITHOUT selecting it first? https://hexdocs.pm/ecto/Ecto.Repo.h...
New
WestKeys
Currently suffering from paralysis by [HTTP client] analysis. This is rather unusual in Elixirland as there tends to be consensus on the ...
New
skosch
To my knowledge, put_in, Map.update etc. all have the one limitation of not automatically creating intermediate keys when needed (for exa...
New

Other popular topics Top

Brian
What is the proper way to load a module from a file in to IEX? In the python world, doing something like this pretty standard: from ....
New
vonH
In asking this question I am more interested about the expressiveness of the language itself and less concerned about the availability of...
New
sergio
I couldn’t find any guides that worked well with Phoenix 1.6.0 and esbuild. I hope this helps people test the waters and eases you into t...
New
KronicDeth
Elixir plugin for JetBrain’s IntelliJ Platform (including Rubymine) This is a plugin that adds support for Elixir to JetBrains IntelliJ...
289 35421 110
New
chrismccord
This release brings a number of exciting features, including integration with the new Phoenix LiveDashboard and Phoenix LiveView. There h...
New
hariharasudhan94
I would like to know what is the best IDE for elixir development?
New
minhajuddin
I have seen a lot of code which picks the first element from a list using Enum.at(0) instead of List.first. Is there a reason why people ...
New
fayddelight
I tried installing elixir 1.11.2 erlang 23.3.4 via asdf in my zsh shell. Enabled the versions locally and globally. When I list them ...
New
shahryarjb
Hello, I have map which I want to convert it to string like this: the map: %{last_name: "tavakkoli", name: "shahryar"} the string I ne...
New
electic
Hi, I am new to Elixir. I am trying to use the DateTime component to insert a date into MySQL however the there seems to be no way to fo...
New

We're in Beta

About us Mission Statement