Usando CTE recursiva com Ecto
Como eu usaria o resultado de uma CTE recursiva em uma consulta que planejo executar com o Ecto? Por exemplo, digamos que eu tenha uma tabela, nós, estruturados da seguinte forma:
-- nodes table example --
id parent_id
1 NULL
2 1
3 1
4 1
5 2
6 2
7 3
8 5
e também tenho outra tabela nodes_users estruturada da seguinte forma:
-- nodes_users table example --
node_id user_id
1 1
2 2
3 3
5 4
Agora, eu quero pegar todos os usuários com um nó em ou acima de um nó específico. Para um exemplo, vamos escolher o nó com o ID 8.
Eu poderia usar o seguinte recursivopostgresql consulta para fazer isso:
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
Isso deve retornar users. * Para os usuários com ID de 1, 2 e 4.
Não tenho certeza de como poderia executar essa mesma consulta usando ecto, de maneira ideal que retornaria uma saída encadeada. Entendo que posso inserir SQL bruto em minha consulta usando a macro de fragmento, mas não sei exatamente para onde isso iria para esse uso ou se essa seria a rota mais apropriada a seguir.
Ajuda e / ou sugestões serão apreciadas!