Postgresql Ltree query to get comment threads nested json/array and build HTML from it

Question

I am building something which has a comment section similar to Reddit. So there can be root comments and users can reply to the root comments, then users can reply to the child comments and so on. Basically nested comment threads.

I used this tutorial and have sort of got it working:

http://www.postgresonline.com/article_pfriendly/173.html

Here's me building the table and inserting data and querying it:

CREATE TABLE comments(commentid integer PRIMARY KEY, commentparentid integer, comment varchar(1000), node_path ltree);

CREATE UNIQUE INDEX idx_comments_node_path_btree_idx ON comments USING btree(node_path);
CREATE INDEX idx_comments_node_path_gist_idx ON comments USING gist(node_path);



CREATE OR REPLACE FUNCTION get_calculated_node_path(param_commentid integer)
  RETURNS ltree AS
$$
SELECT  CASE WHEN s.commentparentid IS NULL THEN s.commentid::text::ltree 
            ELSE get_calculated_node_path(s.commentparentid)  || s.commentid::text END
    FROM comments As s
    WHERE s.commentid = $1;
$$
  LANGUAGE sql;


CREATE OR REPLACE FUNCTION trig_update_node_path() RETURNS trigger AS
$$
BEGIN
  IF TG_OP = 'UPDATE' THEN
        IF (COALESCE(OLD.commentparentid,0) != COALESCE(NEW.commentparentid,0)  OR  NEW.commentid != OLD.commentid) THEN
            -- update all nodes that are children of this one including this one
            UPDATE comments SET node_path = get_calculated_node_path(commentid) 
                WHERE OLD.node_path  @> comments.node_path;
        END IF;
  ELSIF TG_OP = 'INSERT' THEN
        UPDATE comments SET node_path = get_calculated_node_path(NEW.commentid) WHERE comments.commentid = NEW.commentid;
  END IF;

  RETURN NEW;
END
$$
LANGUAGE 'plpgsql' VOLATILE;




CREATE TRIGGER trig01_update_node_path AFTER INSERT OR UPDATE OF commentid, commentparentid
   ON comments FOR EACH ROW
   EXECUTE PROCEDURE trig_update_node_path();

INSERT INTO comments(commentid,commentparentid, comment)
VALUES (1, NULL, 'GreatGreatGrandpa'),
(2, NULL, 'GreatGreatGrandma'),
(3,1, 'GreatGrandpa'),
(4,3, 'Grandpa'),
(5,4, 'Pa'),
(6,5, 'Son'),
(7,6, 'Grandson'),
(13,6, 'Grandson2'),
(8,2, 'GreatGrandma'),
(9,8, 'Grandma'),
(10,9, 'Ma'),
(11,10, 'Daughter'),
(12,11, 'GrandDaughter');

SELECT commentid, commentparentid, comment, node_path FROM comments;

Here's the output:

commentid | commentparentid |      comment      |   node_path    
-----------+-----------------+-------------------+----------------
         1 |                 | GreatGreatGrandpa | 1
         2 |                 | GreatGreatGrandma | 2
         3 |               1 | GreatGrandpa      | 1.3
         4 |               3 | Grandpa           | 1.3.4
         5 |               4 | Pa                | 1.3.4.5
         6 |               5 | Son               | 1.3.4.5.6
         7 |               6 | Grandson          | 1.3.4.5.6.7
        13 |               6 | Grandson2         | 1.3.4.5.6.13
         8 |               2 | GreatGrandma      | 2.8
         9 |               8 | Grandma           | 2.8.9
        10 |               9 | Ma                | 2.8.9.10
        11 |              10 | Daughter          | 2.8.9.10.11
        12 |              11 | GrandDaughter     | 2.8.9.10.11.12

Here's the query and output to get the comment thread as rows:

SELECT c.commentid, c.node_path, array_to_string(array_agg(a.comment ORDER BY a.node_path), '->') As comment_fulltree
FROM comments As c INNER JOIN comments As a
    ON (a.node_path @> c.node_path)
GROUP BY c.commentid, c.node_path, c.comment
ORDER BY c.node_path;


 commentid |   node_path    |                           comment_fulltree                            
-----------+----------------+-----------------------------------------------------------------------
         1 | 1              | GreatGreatGrandpa
         3 | 1.3            | GreatGreatGrandpa->GreatGrandpa
         4 | 1.3.4          | GreatGreatGrandpa->GreatGrandpa->Grandpa
         5 | 1.3.4.5        | GreatGreatGrandpa->GreatGrandpa->Grandpa->Pa
         6 | 1.3.4.5.6      | GreatGreatGrandpa->GreatGrandpa->Grandpa->Pa->Son
        13 | 1.3.4.5.6.13   | GreatGreatGrandpa->GreatGrandpa->Grandpa->Pa->Son->Grandson2
         7 | 1.3.4.5.6.7    | GreatGreatGrandpa->GreatGrandpa->Grandpa->Pa->Son->Grandson
         2 | 2              | GreatGreatGrandma
         8 | 2.8            | GreatGreatGrandma->GreatGrandma
         9 | 2.8.9          | GreatGreatGrandma->GreatGrandma->Grandma
        10 | 2.8.9.10       | GreatGreatGrandma->GreatGrandma->Grandma->Ma
        11 | 2.8.9.10.11    | GreatGreatGrandma->GreatGrandma->Grandma->Ma->Daughter
        12 | 2.8.9.10.11.12 | GreatGreatGrandma->GreatGrandma->Grandma->Ma->Daughter->GrandDaughter

And here's a query and output to get the path to the bottom most leaf comment:

SELECT c.commentid, c.node_path, array_to_string(array_agg(a.comment ORDER BY a.node_path), '-->') As comment_fulltree
FROM (SELECT *
FROM comments AS c1
WHERE NOT EXISTS (
  SELECT *
  FROM comments AS c2
  WHERE c1.node_path @> c2.node_path
    AND c1.node_path <> c2.node_path
)) As c INNER JOIN comments As a
    ON (a.node_path @> c.node_path)
GROUP BY c.commentid, c.node_path, c.comment
ORDER BY c.node_path;

 commentid |   node_path    |                              comment_fulltree                              
-----------+----------------+----------------------------------------------------------------------------
        13 | 1.3.4.5.6.13   | GreatGreatGrandpa-->GreatGrandpa-->Grandpa-->Pa-->Son-->Grandson2
         7 | 1.3.4.5.6.7    | GreatGreatGrandpa-->GreatGrandpa-->Grandpa-->Pa-->Son-->Grandson
        12 | 2.8.9.10.11.12 | GreatGreatGrandma-->GreatGrandma-->Grandma-->Ma-->Daughter-->GrandDaughter

Despite all this, I am having a hard time thinking and figuring out how I can convert this output to the actual HTML to display the comments as nested threads.

I already have built the HTML with a combination of nodejs and EJS views where I am able to display nested comments using nested divs.

I am also familiar with using the "pg-promise" nodejs module to query the database. I understand this part.

But I am not able to figure out how I can get the output from the database and convert it to the HTML. Like right now, the output simply tells me there is a nested thread like GreatGreatGrandma->GreatGrandma->Grandma->Ma->Daughter->GrandDaughter. But since this isn't a JSON or array, I am not sure how to iterate through it to build my HTML?

I am thinking if there is some SQL which gives me a nested JSON, then I can build the HTML. But I don't know how to get it?

https://stackoverflow.com/a/38701519/5962802 – IVO GELOV Aug 16 '19 at 07:18 — IVO GELOV, Aug 16 '19 at 07:18

score 2 · Answer 1 · answered Aug 16 '19 at 08:14

I was able to solve it.

I used the following SQL:

SELECT nlevel(node_path) as depth, commentid, commentparentid, comment, node_path FROM comments ORDER BY node_path;

Which gives me:

depth | commentid | commentparentid |      comment      |   node_path    
--------+-----------+-----------------+-------------------+----------------
      1 |         1 |                 | GreatGreatGrandpa | 1
      2 |         3 |               1 | GreatGrandpa      | 1.3
      3 |         4 |               3 | Grandpa           | 1.3.4
      4 |         5 |               4 | Pa                | 1.3.4.5
      5 |         6 |               5 | Son               | 1.3.4.5.6
      6 |        13 |               6 | Grandson2         | 1.3.4.5.6.13
      6 |         7 |               6 | Grandson          | 1.3.4.5.6.7
      1 |         2 |                 | GreatGreatGrandma | 2
      2 |         8 |               2 | GreatGrandma      | 2.8
      3 |         9 |               8 | Grandma           | 2.8.9
      4 |        10 |               9 | Ma                | 2.8.9.10
      5 |        11 |              10 | Daughter          | 2.8.9.10.11
      6 |        12 |              11 | GrandDaughter     | 2.8.9.10.11.12

using this, I loop through the rows and use the depth field to add the </div> whenever the depth is less than the previous item's depth. This way I am able to create my needed HTML.

Postgresql Ltree query to get comment threads nested json/array and build HTML from it

1 Answers1