Hi,
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.11.1 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
UNION ALL
SELECT c.id, c.parent_id, ct.path || c.id
FROM comments AS c
JOIN comment_tree AS ct ON ct.id = c.parent_id
"""
def list_replies(comments) do
Comment
|> recursive_ctes(true)
|> with_cte("comment_tree", as: fragment(@comment_tree))
|> join(:inner, [c], ct in "comment_tree", on: ct.id == c.id)
|> select([c, ct], %{c | id: c.id})
|> order_by([c, ct], ct.path)
|> Repo.all()
end
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.
