postgresql 将递归CTE与Ecto结合使用

vatpfxk5 于 2023-05-22 发布在 PostgreSQL

关注(0)|答案(2)|浏览(216)

在计划使用Ecto运行的查询中，如何使用递归CTE的结果？例如，假设我有一个表，节点，结构如下：

-- 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

这应该为用户w/ id为1、2和4返回users.*。
我不确定如何使用ecto运行相同的查询，理想情况下，以一种返回可链接输出的方式。我知道我可以使用fragment宏将原始SQL插入到查询中，但我不太确定这种用法的用途，或者这是否是最合适的方法。
帮助和/或建议将不胜感激！

postgresql

来源：https://stackoverflow.com/questions/39383086/using-recursive-cte-with-ecto

2条答案

按热度按时间

emeijp431#

我可以用一个碎片来完成这个。下面是我使用的代码示例。我可能会将此方法移到存储过程中。

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
)

赞(0）回复(0）举报 2023-05-22

8dtrkrch2#

如今，这个问题已经提出了将近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)

赞(0）回复(0）举报 2023-05-22

我来回答

postgresql 将递归CTE与Ecto结合使用

2条答案

相关问题

热门标签

最新问答