分层查询结果中的PostgreSQL订购记录 [英] PostgreSQL Ordering Records in a Hierarchal Query Result

问题描述

我正在尝试做一些应该很简单的事情，但是我缺少了一些东西。我正在尝试按分组和有序返回一些分层数据。我没有附上图片，而是附上图片……听说它们值得一千个单词：

I am trying to do something that should be simple but I'm missing something. I am trying to return some hierarchical data in grouped and ordered. a Rather than trying to explain, I am attaching a picture... heard they are worth a thousand words:

请注意，硬编码值不好，因为主要选择标准将是article_id，然后按照该文章的第一个根级别 article_comment_id的顺序排列，然后是其子节点，然后是下一个根级别 article_comment_id及其子节点

Note that hard coded values are no good because the main selection criteria will be the article_id, and then ordered along the lines of the first 'root level' article_comment_id for the article followed by its sub-nodes, then the next 'root level' article_comment_id and its sub-nodes.

我正在提供示例表和数据，以便您可以理解我的意思，以及显示我要实现的目标的屏幕截图。

I am providing a sample table and data so you can see what I mean, plus a screen capture showing what I am trying to achieve.

CREATE TABLE test_comment
(
  article_comment_id bigserial NOT NULL,
  article_id bigint NOT NULL,
  parent_comment_id bigint,
  comment text NOT NULL,
  comment_depth integer,
  CONSTRAINT test_comment_pkey PRIMARY KEY (article_comment_id )
)

INSERT INTO test_comment (article_comment_id, article_id, parent_comment_id, comment, comment_depth)
VALUES
 (1, 100, 0, 'First Root Comment', 0)
,(5, 100, 0, 'Second Root Comment', 0)
,(2, 100, 1, 'Reply 1 to Root Comment', 1)
,(3, 100, 2, 'Reply 2 to Reply 1', 2)
,(4, 100, 3, 'Reply 3 to Reply 2', 3)
,(6, 100, 2, 'Reply 4 to Reply 1', 2)
,(7, 100, 5, 'Reply 5 to Second Root Comment', 1);

我已经尝试过了，但是没有提供正确的顺序：

I have tried this, but it does not provide the proper order:

with recursive comment_list(article_comment_id, parent_comment_id, comment, article_id) AS (
    select c.article_comment_id, c.parent_comment_id, c.comment, c.article_id
    from test_comment c
    where article_id = 100
  union
    select c.article_comment_id, c.parent_comment_id, c.comment, c.article_id
    from test_comment c, comment_list cl
    where c.article_comment_id = cl.article_comment_id
    )
    select * from comment_list;

这个PostgreSQL特定查询可以和JPA一起使用吗？

And can this PostgreSQL specific query be used with JPA, anyway?

推荐答案

您需要将根商品ID携带到每个节点，并计算层次结构级别才能获得该顺序：

You need to "carry" the root article id down to each node and calculate the hierarchy level to be able to get that ordering:

with recursive comment_list(article_comment_id, parent_comment_id, comment, article_id, level, root_id) AS (
    select c.article_comment_id, 
           c.parent_comment_id, 
           c.comment, 
           c.article_id, 
           0 as level,
           article_comment_id as root_id 
    from test_comment c
    where article_id = 100
      and parent_comment_id = 0

  union

    select c.article_comment_id, 
           c.parent_comment_id, 
           c.comment, 
           c.article_id, 
           cl.level + 1,
           cl.article_comment_id 
    from test_comment c
       join comment_list cl on c.parent_comment_id = cl.article_comment_id
)
select article_comment_id, 
     parent_comment_id, 
     comment, 
     article_id
from comment_list
order by root_id, level;

SQLFiddle： http://www.sqlfiddle.com/#!12/af0d6/4

SQLFiddle: http://www.sqlfiddle.com/#!12/af0d6/4

（顺便说一句：在联盟的递归部分中您的加入有误）

(Btw: you have your join wrong in the recursive part of the union)

这篇关于分层查询结果中的PostgreSQL订购记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！