Использование рекурсивного CTE с Ecto

Question

Как мне использовать результат рекурсивного CTE в запросе, который я планирую выполнить с Ecto? Например, допустим, у меня есть таблица узлов, структурированная так:

-- nodes table example --

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

и у меня также есть еще одна таблица node_users, структурированная так:

-- nodes_users table example --

node_id   user_id
1         1
2         2
3         3
5         4

Теперь я хочу захватить всех пользователей с узлом в или выше определенного узла, для примера давайте выберем узел с идентификатором 8.

Я мог бы использовать следующий рекурсивный запрос postgresql для этого:

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

Это должно вернуть пользователей.* Для пользователей с идентификатором 1, 2 и 4.

Я не уверен, как бы я мог выполнить этот же запрос, используя ecto, в идеале таким образом, чтобы он возвращал цепочку вывода. Я понимаю, что могу вставить необработанный SQL в свой запрос с помощью макроса фрагмента, но я не совсем уверен, куда это пойдет для этого использования или даже если это будет наиболее подходящий путь.

Помощь и / или предложения будут оценены!

Answer 1

Я смог сделать это с помощью фрагмента. Вот пример кода, который я использовал. Я, вероятно, перенесу этот метод в хранимую процедуру.

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
)

Answer 2

Сегодня, спустя почти 7 лет с тех пор, как был задан вопрос, в документации Ecto есть раздел о том, как использовать рекурсивные CTE . Вот как это работает.

Сначала создайте базовый вариант и рекурсивный шаг вашего запроса. Затем используйте объединение этого и передайте егоEcto.Query.with_cte. Вам также необходимо установить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)