Using recursive CTE with Ecto

Question

How would I go about using the result of a recursive CTE in a query I plan to run with Ecto? For example let's say I have a table, nodes, structured as so:

-- nodes table example --

id  parent_id
1   NULL
2   1
3   1
4   1
5   2
6   2
7   3
8   5

and I also have another table nodes_users structured as so:

-- nodes_users table example --

node_id   user_id
1         1
2         2
3         3
5         4

Now, I want to grab all the users with a node at or above a specific node, for the sake of an example let's choose the node w/ the id 8.

I could use the following recursive postgresql query to do so:

WITH RECURSIVE nodes_tree AS (
    SELECT *
    FROM nodes
    WHERE nodes.id = 8
UNION ALL
    SELECT n.*
    FROM nodes n
    INNER JOIN nodes_tree nt ON nt.parent_id = n.id
)
SELECT u.* FROM users u
INNER JOIN users_nodes un ON un.user_id = u.id
INNER JOIN nodes_tree nt ON nt.id = un.node_id

This should return users.* for the users w/ id of 1, 2, and 4.

I'm not sure how I could run this same query using ecto, ideally in a manner that would return a chainable output. I understand that I can insert raw SQL into my query using the fragment macro, but I'm not exactly sure where that would go for this use or if that would even be the most appropriate route to take.

Help and/or suggestions would be appreciated!

Levi

Ecto DSL support was introduced in ecto 3.2. – Plata

Answer 1

I was able to accomplish this using a fragment. Here's an example of the code I used. I'll probably move this method to a stored procedure.

Repo.all(MyProj.User,
  from u in MyProj.User,
  join: un in MyProj.UserNode, on: u.id == un.user_id,
  join: nt in fragment("""
  (
    WITH RECURSIVE node_tree AS (
      SELECT *
      FROM nodes
      WHERE nodes.id = ?
    UNION ALL
      SELECT n.*
      FROM nodes n
      INNER JOIN node_tree nt ON nt.parent_id == n.id
    )
  ) SELECT * FROM node_tree
  """, ^node_id), on: un.node_id == nt.id
)

Levi

Answer 2

These days, almost 7 years since the question was asked, Ecto's documentation features a section about how to use recursive CTEs. Here's how it works.

First, construct the base case and the recursive step of your query. Then, use the union of that and pass it to Ecto.Query.with_cte. You also need to set recursive_ctes(true):

base_case =
  from(row in "nodes", where: row.id == ^node_id)

recursive_step =
  from(n in "nodes", join: nt in "node_tree", on: nt.parent_id == n.id)

node_tree = base_case |> union(^recursive_step)

MyProj.User
|> recursive_ctes(true)
|> with_cte("node_tree", as: ^node_tree)
|> join(:inner, [u], un in MyProj.UserNode, on: u.id == un.user_id)
|> join(:inner, [u, un], nt in "node"tree", on: un.node_id == nt.id)

Bucaramanga