CODEWITHSUNDEEP
sqlpostgresqlhierarchypostgresql-9.1
Bill Rosmus
Bill Rosmus

Reputation: 3011

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:

Order of data in the database vs the order the resultset needs to be in.

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;

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

Upvotes: 2

Views: 323

Answers (1)

user330315
user330315

Reputation:

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

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

Upvotes: 4

Related Questions