Database schema design for posts, comments and replies

Question

In my previous project I had posts and comments as two tables:

post

• id
• text
• timestamp
• userid

comment

• id
• message
• timestamp
• userid
• postid

Now I've got to design replies to comments. The replies is just one level, so users can only reply to comments, not to replies. The tree structure is only 1 level deep. My first idea was to use the same comment table for both comments and replies. I added a new column though:

comment

• id
• message
• timestamp
• userid
• postid
• parentcommentid

Replies have parentcommentid set to the parent comment they belong. Parent comments don't have it (null)

Retrieving comments for a given post is simple:

but this time I need another query to find out the comment replies. This has to be done for each comment:

This doesn't seem to be a good solution, is there a way to have a single query which returns the complete list of comments/replies in the correct order? (dictated by the timestamp and the nesting)

Solution 1

You may use join and achieve result in single query like I provided below:

  SELECT *, cc.message as replied_message 
    FROM `post` 
    JOIN comment as c 
      ON c.postid = post.id 
    JOIN comment as cc 
      ON cc.id = c.parentcommentid 
ORDER BY c.timestamp DESC, cc.timestamp DESC;

Please note that, it works correctly only if 1 comment have 1 reply only.multiple replies on single comment will not support by this query

Solution 2

If you're using a database that supports JSON or object aggregation, you can get a nicer result from the query where each top-level comment is a row (and is not duplicated), and the replies are nested in an array/JSON within each row.

This gives you flexibility with what you do with it and also makes it easier to ensure the ordering and nesting is correct.

An example using Postgres:

SELECT
  p.id AS post_id,
  c.id AS comment_id,
  c.message,
  JSON_AGG(
    JSON_BUILD_OBJECT('comment', r.comment, 'timestamp', r.timestamp)
    ORDER BY r.timestamp
  ) AS child_comments
FROM
  post AS p
  INNER JOIN comment AS c
    ON c.post_id = p.id
  LEFT JOIN comment AS r
    ON r.parent_id = c.id
WHERE
  post.id = <some id>
  AND c.parent_id IS NULL
GROUP BY
  post.id,
  c.id,
  c.message
ORDER BY
  c.timestamp DESC
;

Note that, as above, this example will only retrieve the top-level and their first-level replies. It won't get replies to replies. You can use recursive commands or additional subqueries to do that.

Comments

[Gianluca Ghettini]

this is the query I was thinking.. however, comments should be sorted by timestamp and replies within comments should be sorted as well. Oh, only 1 reply? Unfortunately on my scenario a comment can have a number of replies

[Rupal Javiya]

Then it would be not possible in single query and if we still try to do it via join, then it may gives you duplicate rows because every time your replied commented data will be different but those all will provide result with original message.decide first which way you want to move 1) single query with records duplication 2) 2 separate queries, 1st for message and 2nd for replies

[Gianluca Ghettini]

that's my actual question: can it be done with 1 query or not? With 1 query is it possible to reconstruct the tree structure or not?

[Arth]

@GianlucaGhettini This will support multiple comments and multiple replies, you just have to be careful when looping through the result set as the original comment will be duplicated alongside the multiple replies. N.B. I'd swap the second JOIN for a LEFT JOIN in the case a comment has no replies. Also I'm guessing you want to put an id filter on post as you are probably only looking for one post.

[Rupal Javiya]

SELECT , cc. FROM post JOIN comment as c on c.postid = post.id JOIN comment as cc on cc.parentcommentid = c.id ORDER BY c.timestamp DESC, cc.timestamp DESC;

[Rupal Javiya]

Previous comment query I posted, will provide you all replies on single post with data duplication as I mentioned earlier. you may restructure it as per your need OR just use with simple for each should work also. For reconstruct, you may use inbuilt array function like array_walk (php.net/manual/en/function.array-walk.php) but it also ultimately do foreach on your array

[md-shah]

Thanks, mate. This worked for the grouped replies. GROUP BY is the key.