Recursive Comment Tree review/help/advice?


So about 5 days ago I started trying to figure out to make a recurisive tree structure for comments. I know Ltree exists but every time I tried to set it up it just melted my project. I have minimal knowledge of SQL and have never dealt with tree structures. I learned about using Left/Right nodes but feel like it would be a bit heavy transaction wise to use for comments if you have a lot of them.

I used Ecto.Query — Ecto v3.9.5 to get to this point and I feel like it works or is nearly there, but I’m kind of not sure how to extensively test it, nor am I sure if I’ve done it “correctly” in terms of it being efficient/scalable or using whatever standard practices exist for this.

I’m basically just asking have I reinvented the wheel correctly to anyone who has done this in Elixir or elsewhere before. Are there any obvious mistakes or shortfalls with what I’ve done you can see?

I normally do queries (from c in Comment etc so I’m kind of unsure about the format being used for my list_replies function and whether I’ve included anything I didn’t need to or added things I don’t need. I pretty much did this through “monkeys and type writers” method.

  @comment_tree """
    SELECT id, parent_id, ARRAY[id] AS path
    FROM comments
    WHERE parent_id IS NULL
    SELECT, c.parent_id, ct.path ||
    FROM comments AS c
    JOIN comment_tree AS ct ON = c.parent_id

  def list_replies(comments) do
    |> recursive_ctes(true)
    |> with_cte("comment_tree", as: fragment(@comment_tree))
    |> join(:inner, [c], ct in "comment_tree", on: ==
    |> select([c, ct], %{c | id:})
    |> order_by([c, ct], ct.path)
    |> Repo.all()

1.1 and 1.2 are out of order because I typed 1.2 first for some reason.

So yeah, any feedback appreciated. I feel like I’m really close after a stressful 5 days.

Here’s a less confusing image. Made another post and got the order more sensical

This library might be of interest to you: GitHub - coryodaniel/arbor: Ecto elixir adjacency list and tree traversal. Supports Ecto versions 2 and 3.


(edit: whenever I’m looking for something that feels like it should be a solved problem, I take a quick peek through GitHub - h4cc/awesome-elixir: A curated list of amazingly awesome Elixir and Erlang libraries, resources and shiny things. Updates: to see if there’s a library I should be checking out)


Wow that’s very cool
I tend to be pretty unlucky when trying to use things that already exist though. I tried to set up Ltree and it sent my entire project into meltdown :slight_smile:

I’ll definitely check this out though