Блог

У меня есть две таблицы, подобные этой

 CREATE TABLE posts (
  id PRIMARY KEY,
  text varchar
);

CREATE TABLE comments (
  id PRIMARY KEY,
  text varchar,
  post_id integer,
  reply_to integer -- recursive reference
);
  

мне нужен запрос, который преобразует эти связанные данные в график, подобный вложенному json, вот так

 [
    {
        text: 'post',
        comments: [
            {
                text: 'normal comment',
                replies: [
                    { text: 'reply comment' }
                ]
            },
            { text: 'other normal comment' }   
        ]
    }
]
  

пока что у меня есть этот запрос, который выдает мне массив комментариев, но ответов нет

 select row_to_json(t)
from (
    select id, text, created_at,
        (
            select array_to_json(array_agg(row_to_json(d)))
            from (
                select id, text, post_id, replied_to, created_at
                from comments
                where post_id = posts.id and replied_to is null
            ) d
        ) as comments
    from posts
) t
  

но не могу понять, как также запрашивать ответы, чтобы у меня было полное дерево, пожалуйста, помогите

Вам нужно использовать рекурсивный запрос, чтобы следовать рекурсии идентификаторов.

 SELECT row_to_json(t)
FROM (
  SELECT id, text,
    (
      SELECT array_to_json(array_agg(row_to_json(x))) AS comments 
      FROM (                               
        SELECT id, text, post_id, reply_to,
          ( SELECT array_to_json(array_agg(row_to_json(d)))
            FROM (
                    WITH RECURSIVE temp
                    AS
                    (
                      SELECT id, text, post_id, reply_to
                      FROM comments
                      WHERE reply_to IS NULL
                        AND post_id = posts.id
                        AND id = c.id
                      UNION ALL
                      SELECT nxt.id, nxt.text, nxt.post_id, nxt.reply_to
                      FROM temp prv
                      JOIN comments nxt ON prv.id = nxt.reply_to
                      WHERE nxt.reply_to IS NOT NULL
                     ) SELECT * FROM temp WHERE reply_to IS NOT NULL
                   ) d
           ) as replies
        FROM comments c
        WHERE c.reply_to IS NULL
          AND c.post_id = posts.id
      ) x
    )
  FROM posts
) t
  

Вот SQLFiddle.

Полуформатированный вывод:

 {"id":1,
 "text":"Test post",
 "comments":[{
     "id":1,
     "text":"Test comment",
     "post_id":1,
     "reply_to":null,
     "replies":[{
         "id":2,
         "text":"Test reply",
         "post_id":1,
         "reply_to":1
         },
        {"id":3,
         "text":"Reply reply",
         "post_id":1,
         "reply_to":2
        }
     ]},
     {"id":4,
      "text":"Comment 2",
      "post_id":1,
      "reply_to":null,
      "replies":[{
          "id":5,
          "text":"Reply 2",
          "post_id":1,
          "reply_to":4
      }]
    }
]}
  

Это круто.

Если вы сможете переименовать reply_to в parent_id , это будет описывать взаимосвязь прямо в имени столбца.