在 Postgres 中订购 WITH RECURSIVE 查询

发布于 2024-09-09 05:43:08 字数 698 浏览 1 评论 0原文

我正在 Postgres 中执行递归查询来检索电子邮件列表及其线程子电子邮件，如下所示：

WITH RECURSIVE cte (id, title, path, parent_id, depth)  AS (
  SELECT  id, 
          title,
          array[id] AS path,
          parent_id, 
          1 AS depth
  FROM    emails
  WHERE   parent_id IS NULL

  UNION ALL

  SELECT  emails.id,
          emails.title,
          cte.path || emails.id,
          emails.parent_id, 
          cte.depth + 1 AS depth
  FROM    emails
          JOIN cte ON emails.parent_id = cte.id
)
SELECT id, title, path, parent_id, depth FROM cte
ORDER BY path;

在查找子电子邮件之前如何更改列表的顺序（例如按标题排序）。显然我需要保留外部 ORDER BY 以便按树顺序检索列表，并且 Postgres 不允许我在 UNION ALL 之前插入 ORDER BY 子句。

谢谢，

原文

I'm executing a recursive query in Postgres to retrieve a list of emails and their threaded children as follows:

WITH RECURSIVE cte (id, title, path, parent_id, depth)  AS (
  SELECT  id, 
          title,
          array[id] AS path,
          parent_id, 
          1 AS depth
  FROM    emails
  WHERE   parent_id IS NULL

  UNION ALL

  SELECT  emails.id,
          emails.title,
          cte.path || emails.id,
          emails.parent_id, 
          cte.depth + 1 AS depth
  FROM    emails
          JOIN cte ON emails.parent_id = cte.id
)
SELECT id, title, path, parent_id, depth FROM cte
ORDER BY path;

How would go about changing the order of the list (for example sorting on title) before finding children emails. I obviously need to keep the outer ORDER BY so that the list is retrieved in it's tree order, and Postgres won't let me insert an ORDER BY clause before the UNION ALL.

Thanks,

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

故事灯 2024-09-16 05:43:08

这是未经测试的，但通常我可以在并集之前添加任何 ORDER BY，只要有括号......

WITH RECURSIVE cte (id, title, path, parent_id, depth)  AS (
(  SELECT  id, 
          title,
          array[id] AS path,
          parent_id, 
          1 AS depth
  FROM    emails
  WHERE   parent_id IS NULL
  ORDER BY title
)
  UNION ALL

  SELECT  emails.id,
          emails.title,
          cte.path || emails.id,
          emails.parent_id, 
          cte.depth + 1 AS depth
  FROM    emails
          JOIN cte ON emails.parent_id = cte.id
)
SELECT id, title, path, parent_id, depth FROM cte
ORDER BY path;

This is untested, but usually i can add any ORDER BY before a union so long as there are parentheses...

WITH RECURSIVE cte (id, title, path, parent_id, depth)  AS (
(  SELECT  id, 
          title,
          array[id] AS path,
          parent_id, 
          1 AS depth
  FROM    emails
  WHERE   parent_id IS NULL
  ORDER BY title
)
  UNION ALL

  SELECT  emails.id,
          emails.title,
          cte.path || emails.id,
          emails.parent_id, 
          cte.depth + 1 AS depth
  FROM    emails
          JOIN cte ON emails.parent_id = cte.id
)
SELECT id, title, path, parent_id, depth FROM cte
ORDER BY path;

回复收藏 0 原文

〆一缕阳光ご 2024-09-16 05:43:08

创建一个由查询的第一部分组成的视图，按标题排序。也许是这样的？

      CREATE VIEW title_Sort AS
      SELECT  id,  
      title, 
      array[id] AS path, 
      parent_id,  
      1 AS depth  
      FROM    emails 
      WHERE   parent_id IS NULL 
      ORDER BY title;

然后像之前一样将该视图与其他查询联合起来。我认为这会起作用。现在在我的上网本上，所以我无法测试：/

Create a view consisting of the first part of your query, ordered by title. Maybe something like this?

      CREATE VIEW title_Sort AS
      SELECT  id,  
      title, 
      array[id] AS path, 
      parent_id,  
      1 AS depth  
      FROM    emails 
      WHERE   parent_id IS NULL 
      ORDER BY title;

Then UNION ALL that view with your other query as you did before. I think that will work. On my netbook right now so I can't test :/

回复收藏 0 原文

~没有更多了~