问题描述
我正在尝试做一些应该很简单的事情,但是我缺少了一些东西。我正在尝试按分组和有序返回一些分层数据。我没有附上图片,而是附上图片……听说它们值得一千个单词:
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:
SQLFiddle: http://www.sqlfiddle.com/#!12/af0d6/4
(顺便说一句:在联盟的递归部分中您的加入有误)
(Btw: you have your join wrong in the recursive part of the union)
这篇关于分层查询结果中的PostgreSQL订购记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!