在Ecto中使用递归CTE [英] Using recursive CTE with Ecto

问题描述

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

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

我还有另外一个表nodes_users，其结构如下:

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

现在，我想抓住一个节点位于特定节点或特定节点之上的所有用户，为了举例说明，我们选择ID为8的节点.

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.

我可以使用以下递归postgresql 查询为此:

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

这应该返回用户.* ID为1、2和4的用户.

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

我不确定如何使用ecto运行相同的查询，理想情况下，该查询将返回可链接的输出.我知道我可以使用片段宏将原始SQL插入查询中，但是我不确定这将在哪里使用，或者这是否是最合适的选择.

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!

推荐答案

我能够使用片段来完成此任务.这是我使用的代码示例.我可能会将这种方法移到存储过程中.

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
)

这篇关于在Ecto中使用递归CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！