我该如何在使用Ecto运行的查询中使用递归CTE的结果?例如,假设我有一个名为nodes的表,结构如下:
-- nodes table example --
id parent_id
1 NULL
2 1
3 1
4 1
5 2
6 2
7 3
8 5
而且我还有另一个名为nodes_users的表,结构如下:
-- nodes_users table example --
node_id user_id
1 1
2 2
3 3
5 4
现在,我想获取所有具有节点编号大于或等于某个特定节点的用户,为了举例,让我们选择id为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
这应该返回id为1、2和4的用户的 users.*。
我不确定如何使用 Ecto 运行同样的查询,最好以可链接的输出方式返回。我知道可以使用 fragment 宏将原始 SQL 插入到查询中,但我不确定在此用例中应该放置它,或者它是否是最合适的路线。
帮助和/或建议将不胜感激!