How do you do a recursive query on a self-referential table in Ecto?

Questions / Help
1

Greetings, everyone!

I’m trying to translate this SQL query into Ecto but it’s kicking me in the cheeks:

WITH RECURSIVE tree AS (
      SELECT id, parent_id, name
      FROM tags
      WHERE id = $1
      UNION ALL
      SELECT child.id, child.parent_id, child.name
      FROM tags child
      JOIN tree parent ON parent.id = child.parent_id
)
SELECT *
FROM tree

It’s a simple table containing only the three fields in the SELECT statement.

I’ve tried various ideas that popped up while reading the documentation, including using fragment/1 and with_cte/3, but the best I’ve been able to produce so far is compiler errors.

Thanks in advance for your help.

3 Likes
2

I have rarely used with_cte but never had issues, what is the problem you are witnessing exactly?

3

Is this your full fragment code? Or is it like the below?

order_by: fragment(
"""
(
WITH RECURSIVE tree AS (
     SELECT id, parent_id, name
     FROM tags
     WHERE id = $1
     UNION ALL
     SELECT child.id, child.parent_id, child.name
     FROM tags child
     JOIN tree parent ON parent.id = child.parent_id
)
SELECT *
FROM tree
)
"""

If I recall correctly, you need brackets and “”" around the WITH RECURSIVE to get it to work properly without throwing errors. I was messing around with this last month so may be wrong.

Either way, I ended up just using this instead.

  @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

The above obviously isnt the full code, but its part of the code that lets create a recursive comment tree like in the below image.

Comment2
Comment2690×1285 30 KB

4 Likes
4

Thanks for your help! Your suggestion worked. The problem was that I had my SQL fragment defined as a function variable instead of as a module attribute, which caused this compiler error:

** (Ecto.Query.CompileError) to prevent SQL injection attacks, fragment(...) does not allow strings to be interpolated as the first argument via the `^` operator, got: `tag_tree`