用于评论和评论回复的mysql结构

I've been thinking about this one for quite some time now, I need a way to add replies to comments in the database but I'm not sure how to proceed.

我一直在考虑这个问题很长一段时间，我需要一种方法来添加对数据库中的注释的回复，但我不知道如何继续。

This is my currently comment table (doesn't say much but its a start):

这是我目前的评论表（并没有多说但是它的开头）：

CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `comment` text,
  `user_id` int(12) DEFAULT NULL,
  `topic_id` int(12) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;

and here is my current query:

这是我当前的查询：

SELECT c.id, c.comment, c.user_id, u.username, u.photo
FROM (comments c)
JOIN users u ON c.user_id = u.id
WHERE c.topic_id = 9

One option would be to create a new table called "comment_replies" but I'm not sure If I'm able to select all the comments and comment replies in one query, and If I add a new column called "reply" I'm not sure how to sort them to get each comment with each reply.

一种选择是创建一个名为“comment_replies”的新表，但我不确定如果我能够在一个查询中选择所有注释和注释回复，并且如果我添加一个名为“回复”的新列我就是不确定如何对它们进行排序以获得每个回复的每条评论。

I would love to get some advice on how to deal with this.

我很想得到一些关于如何处理这个问题的建议。

Edit:

编辑：

Following the below answers about adding parent_comment_id result in this kind of array from 1 comment and 2 replies:

以下答案关于在1条评论和2条回复中添加parent_comment_id结果这种数组：

array(2) {
  [0]=>
  object(stdClass)#17 (7) {
    ["id"]=>
    string(2) "26"
    ["comment"]=>
    string(36) "adding a comment from the admin page"
    ["user_id"]=>
    string(2) "16"
    ["ts"]=>
    string(10) "1249869350"
    ["username"]=>
    string(5) "Admin"
    ["photo"]=>
    string(13) "gravatar2.png"
    ["reply"]=>
    string(23) "There is no admin page!"
  }
  [1]=>
  object(stdClass)#18 (7) {
    ["id"]=>
    string(2) "26"
    ["comment"]=>
    string(36) "adding a comment from the admin page"
    ["user_id"]=>
    string(2) "16"
    ["ts"]=>
    string(10) "1249869350"
    ["username"]=>
    string(5) "Admin"
    ["photo"]=>
    string(13) "gravatar2.png"
    ["reply"]=>
    string(13) "Yes there is!"
  }
}

How should I process this array to work with it, Is it possible to separate the comment from the replies?

我应该如何处理这个数组来处理它，是否可以将评论与回复分开？

6 个解决方案

#1

If you want people to be able to reply to the replies (i.e. have a hierarchy of replies such as you would see in, say, an online message forum), then I would add an optional parent_comment_id field to the comments table.

如果您希望人们能够回复回复（例如，您可以在在线消息论坛中看到回复的层次结构），那么我会在评论表中添加可选的parent_comment_id字段。

Your table would look like this

你的桌子看起来像这样

`CREATE TABLE IF NOT EXISTS `comments` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `parent_comment_id` int(12) NULL,
  `comment` text,
  `user_id` int(12) DEFAULT NULL,
  `topic_id` int(12) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=27 ;`

Your query showing all comments and replies would be something like:

您显示所有评论和回复的查询将类似于：

SELECT c.id, c.comment, r.comment as reply, c.user_id, u.username, u.photo
FROM (comments c)
JOIN users u ON c.user_id = u.id
LEFT JOIN comments r ON c.id = r.parent_comment_id
WHERE c.topic_id = 9

Note however that with this query your replies would also show up not only in the 'reply' column, but also in the 'comment' column as additional rows each with zero or more replies.

但请注意，对于此查询，您的回复不仅会显示在“回复”列中，还会显示在“注释”列中，作为附加行，每个行都有零个或多个回复。

To show the username of the users who replied to a comment, you will need to join twice to the users table (first for the user who posted the original comment, and again for the user(s) who replied). Try this query to show the usernames of the users who replied:

要显示回复评论的用户的用户名，您需要两次加入users表（首先是发布原始评论的用户，再次是回复的用户）。尝试此查询以显示回复的用户的用户名：

SELECT c.id, c.comment, c.user_id, u.username, u.photo, r.comment as reply, r.user_id as reply_user_id, 
u2.username as reply_username, u2.photo as reply_photo
FROM (comment c)
JOIN users u ON c.user_id = u.id
LEFT JOIN comments r ON c.id = r.parent_comment_id
JOIN users u2 ON r.user_id = u2.id
WHERE c.topic_id = 9

#2

Add a parent_comment_id column to your comments table. Make it optional. When you query, you can join all child comments to each parent. As a bit of selective denormalization (slight redundancy) you can make sure topic_id is set on the child comments as well, letting you pull them all a bit easier (assuming you're going to display all child comments in the main comment thread and not via smaller ajax requests).

将parent_comment_id列添加到注释表中。让它可选。查询时，您可以将所有子注释连接到每个父级。作为一些选择性非规范化（轻微冗余），您可以确保在子注释上设置topic_id，让您更轻松地将它们拉出来（假设您将在主注释线程中显示所有子注释而不是通过较小的ajax请求）。

Build the presentation however you need, toss the results into memcached (or a flat file, or memory... however you're caching) and you're set.

根据需要构建演示文稿，将结果抛入memcached（或平面文件或内存......但是你正在缓存）并且你已经设置好了。

#3

I decided to add the parent_id column in the database and instead of left joining the replies I just selected all the comments at once to later on sort the comments and replies with server-side code, heres the query:

我决定在数据库中添加parent_id列，而不是左边加入回复，我刚刚选择了所有注释，以便稍后对服务器端代码的注释和回复进行排序，继承查询：

SELECT c.*, u.username, u.photo
FROM (comments c)
JOIN users u ON c.user_id = u.id
WHERE c.topic_id = 9
ORDER BY c.id ASC

Now I pass the query result to the below function so that every reply will be added as an array inside the comment array, so basically it returns a multidimensional array.

现在我将查询结果传递给下面的函数，这样每个回复都将作为数组添加到注释数组中，所以基本上它返回一个多维数组。

function sort_comments($ar)
{
    $comments = array();
    foreach($ar as $item)
    {
        if(is_null($item['parent_id'])) $comments[] = $item;
        else 
        {
            $parent_array = array_search_key($item['parent_id'],$comments,'id');
            if($parent_array !== false) $comments[$parent_array]['replies'][] = $item;
        }
    }
    return $comments;
}

#4

A comment reply is a comment with a parent comment_id. Try adding comment_id as a field to your comments table. What you will get is a tree-like structure.

评论回复是父评论_id的评论。尝试将comment_id作为字段添加到评论表中。你将得到的是树状结构。

If you wish to retrieve an entire tree of comments, your best bet is to use a nested set (https://wiki.htc.lan/Hierarchy_model). But that's a more complicated solution.

如果您希望检索整个评论树，最好的办法是使用嵌套集（https：//wiki.htc.lan/Hierarchy_model）。但这是一个更复杂的解决方案。

Here's some more info from MySQL: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

以下是来自MySQL的更多信息：http：//mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

#5

Looks like you are working with WordPress, adding a parent_comment_id would have been an ideal solution, but not in this case.

看起来你正在使用WordPress，添加parent_comment_id本来是一个理想的解决方案，但在这种情况下不是。

Firstly, I don't think modifying the WordPress basic tables is a good idea. Secondly, you'll end-up with a complex code that will break with wordpress updates.

首先，我不认为修改WordPress基本表是个好主意。其次，你最终会得到一个复杂的代码，它会破坏wordpress更新。

Best is use a plugin like Intense Comments

最好使用像强烈评论这样的插件

Still if you want to create your own solution, I would say create another table for comment replies. a) Your new table would look like this

仍然如果你想创建自己的解决方案，我会说创建另一个表评论回复。 a）你的新桌子看起来像这样

`CREATE TABLE IF NOT EXISTS `comment_replies` (
  `id` int(12) NOT NULL AUTO_INCREMENT,
  `parent_comment_id` int(12) NULL,
  `comment` text,
  `user_id` int(12) DEFAULT NULL,
  `topic_id` int(12) NOT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `topic_id` (`topic_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8

b) You'd fetch them this way

b）你这样取他们

$comment_ids = array_map( 'intval', array_keys( $comments ) );
sort( $comment_ids );
$comments_id_list = join( ',', $comment_ids );

$query = "SELECT c.id, c.comment, c.user_id, u.username, u.photo
FROM (comment_replies c)
JOIN users u ON c.user_id = u.id
WHERE c.parent_comment_id IN ( $comments_id_list )"

$replies = $wpdb->get_results($query);
$replies_by_parent = array();

foreach ( array_keys( $replies ) as $i ) {          
    $replies_by_parent [$replies[$i]->id] = array();
}
foreach ( array_keys( $replies ) as $i ) {          
    $replies_by_parent [$replies[$i]->id][] =& $replies[$i];
}

c) Now within your comments loop you can get the replies like this

c）现在在你的评论循环中你可以得到这样的回复

foreach (  $replies_by_parent [$parent_id] as $reply ) {            
        echo $reply->comment;
    }

#6

this seems all good but what about if the table contained over a million rows? and some comments could be hundreds of thousands of rows apart. how will these queries perform?

这似乎都很好，但如果表包含超过一百万行呢？并且一些评论可能相隔数十万行。这些查询将如何执行？