帖子，评论和回复的数据库架构设计

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:

现在我必须设计回复评论。回复只是一个级别,因此用户只能回复评论,而不能回复。树结构只有1层深。我的第一个想法是对评论和回复使用相同的评论表。我添加了一个新专栏:

comment

id
message
timestamp
userid
postid
parentcommentid

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

回复已将parentcommentid设置为它们所属的父注释。家长评论没有(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)

这似乎不是一个好的解决方案,有没有办法让一个查询以正确的顺序返回完整的评论/回复列表? (由时间戳和嵌套决定)

1 个解决方案

#1

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

你可以在我下面提供的单个查询中使用join和achieve结果:

  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

请注意,只有当1个评论只有1个回复时,它才能正常工作。此查询不支持单个评论的多个回复

#1