嵌套注释系统mysql订购 [英] Nested comment system mysql ordering
问题描述
好的，我知道有很多方法可以完成评论。我选择的方式是像这样建立一个表格。
Ok, I realize there are many ways to accomplish comments. The way I have chosen is a single table set up like this.
id comment date time orig_comment
1 Hello 03-01-2013 10:10:10 0
2 Hello 03-02-2013 10:10:10 0
3 Hello 03-03-2013 10:10:10 1
因此要弄清楚有一个第一级，然后用户可以回复该评论（这是仅有的两个级）。我给每个唯一的递增ID，并指定orig_comment。如果orig_comment为 0，则它是基本级别的注释；如果嵌套，则orig_comment将为原始注释的ID。很简单。因此，我当然需要对评论进行排序。
So to clearify there is a first level and then users can reply to that comment(these are the only two levels). I give each unique incrementing id, and specify orig_comment. If orig_comment is "0" then it is a base level comment, if it is nested then orig_comment will be the id of the original comment. Simple enough. So I need the comments to be ordered of course. Here is how I do this as of now.
mysql_query("SELECT Comments.* FROM Comments ORDER BY
IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC")
这可以获取最新检索到的最新评论（这是我想要的），但是当涉及到嵌套评论时，它按最早的顺序排序（不是我所需要的）。我需要将主要评论从最新到最旧排序，所有回复也应从最新到最旧排序。我尝试不成功地修改我的查询来执行此操作，但无法解决。
This works to get the newest comments retrieved first(which is what I want), but when it comes to nested comments it orders them by oldest first(not what I need). I need to order primary comments from newest to oldest, and any replies should be ordered also from newest to oldest. I have tried without success to modify my query to do this but cannot figure it out.
作为一个附带问题，从可用性角度来看，此系统是否有意义？您认为以这种方式订购嵌套注释会令人困惑吗？我想这样做，因为我的回复按钮位于基本注释上，单击后在基本注释的下方添加一个文本区域。我只是认为，如果您的帖子也恰好位于基本评论的下方，而不是一路扔到嵌套回复的底部，将会更容易。对此有任何想法吗？
As a side question does this system make sense from a usabality standpoint? Do you think ordering nested comments that way would be confusing? I wanted to do it this way because my reply button is located on the base comment, and once clicked adds a textarea right below the base comment. I just thought it would be easier to see your post if it too came right below the base comment instead of being thrown all the way to the bottom of the nested replies. Any thoughts on this?
还想解决我使用此页面发表评论的另一个问题。我可以使用一个简单的限制来仅获取x个这样的注释：
Also want to address another question I have using this to page comments. I can use a simple limit to get only the x number of comments like this:
mysql_query("SELECT Comments.* FROM Comments ORDER BY
IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC
LIMIT 0, $page")
显然，此方法没有注意每个主评论的回复数。因此，我最终切断了对最后一个主要评论的答复。我只想在orig_comment ='0'时强制执行该限制，这样无论评论有多少回复，它都会显示所有评论。我尝试过
This method obviously does not pay attention to the number of replies to each main comment. So I end up cutting off replies to the very last main comment. I would like to enforce the limit only when orig_comment='0', so that no matter how many replies there are to a comment it will show all of them. I tried
mysql_query("SELECT Comments.* FROM Comments ORDER BY
IF(Comments.orig_comment = 0, Comments.id, Comments.orig_comment)DESC,Comments.id ASC
LIMIT 0, SELECT COUNT(id)FROM Comments WHERE orig_comment='0' LIMIT $page")
这会引发语法错误。
推荐答案
考虑此示例...
DROP TABLE IF EXISTS comments;
CREATE TABLE comments
(comment_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,comment VARCHAR(50) NOT NULL
,comment_date DATETIME
,parent_id INT NULL
);
INSERT INTO comments VALUES
(1 ,'Hello', '2013-03-01 10:10:10',NULL),
(2 ,'Bonjour', '2013-03-02 10:10:10',NULL),
(3 ,'How are you?', '2013-03-03 10:10:10',1),
(4 ,'I\'m fine thank you, and you?', '2013-03-04 10:10:10',1),
(5 ,'Ça va?', '2013-03-05 10:10:10',2),
(6 ,'Je vais bien, merci, et toi?', '2013-03-06 10:10:10',2),
(7 ,'Yes, not too bad thanks', '2013-03-07 10:10:10',1),
(8 ,'Oui, comme ci comme ça.', '2013-03-08 10:10:10',2),
(9 ,'Bon, à bientôt.', '2013-03-09 10:10:10',2),
(10 ,'See you soon', '2013-03-10 10:10:10',1);
SELECT *
FROM comments
x
JOIN comments y
ON y.parent_id = x.comment_id
ORDER
BY x.comment_date
, y.comment_date;
+------------+---------+---------------------+-----------+------------+------------------------------+---------------------+-----------+
| comment_id | comment | comment_date | parent_id | comment_id | comment | comment_date | parent_id |
+------------+---------+---------------------+-----------+------------+------------------------------+---------------------+-----------+
| 1 | Hello | 2013-03-01 10:10:10 | NULL | 3 | How are you? | 2013-03-03 10:10:10 | 1 |
| 1 | Hello | 2013-03-01 10:10:10 | NULL | 4 | I'm fine thank you, and you? | 2013-03-04 10:10:10 | 1 |
| 1 | Hello | 2013-03-01 10:10:10 | NULL | 7 | Yes, not too bad thanks | 2013-03-07 10:10:10 | 1 |
| 1 | Hello | 2013-03-01 10:10:10 | NULL | 10 | See you soon | 2013-03-10 10:10:10 | 1 |
| 2 | Bonjour | 2013-03-02 10:10:10 | NULL | 5 | Ça va? | 2013-03-05 10:10:10 | 2 |
| 2 | Bonjour | 2013-03-02 10:10:10 | NULL | 6 | Je vais bien, merci, et toi? | 2013-03-06 10:10:10 | 2 |
| 2 | Bonjour | 2013-03-02 10:10:10 | NULL | 8 | Oui, comme ci comme ça. | 2013-03-08 10:10:10 | 2 |
| 2 | Bonjour | 2013-03-02 10:10:10 | NULL | 9 | Bon, à bientôt. | 2013-03-09 10:10:10 | 2 |
+------------+---------+---------------------+-----------+------------+------------------------------+---------------------+-----------+
这篇关于嵌套注释系统mysql订购的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
