多层评论回复:显示和存储 [英] Multi-tiered Comment Replies: Display and Storage
问题描述
因此，我正在尝试创建一个评论系统，在其中您可以回复已经回复的评论(从理论上讲，您可以创建无限个回复线程).我希望它们按时间顺序显示(最新显示在最上方)，但是当然，答复应该直接在原始注释的下面.如果有多个评论回复同一评论，则回复也应按时间顺序排列(仍在原始评论下方).我还想将注释组的数量(一组带有一个完全不答复的单个注释的注释)的数量限制为25个.我应该如何设置MySQL表，以及我将使用哪种查询类型?提取我想要的东西?
So I'm trying to create a comment system in which you can reply to comments that are already replies (allowing you to create theoretically infinite threads of replies). I want them to display in chronological order (newest on top), but of course the replies should be directly underneath the original comment. If there are multiple comments replying to the same comment, the replies should also be in chronological order (still underneath the original comment). I also want to limit the number of comment groups (a set of comments with a single comment that is not a reply at all) to, say, 25. How should I set up the MySQL table, and what sort of query would I use to extract what I want?
这是我的数据库的简化版本:
ID int(11)NOT NULL AUTO_INCREMENT，
DatePosted datetime NOT NULL，
InReplyTo int(11)NOT NULL缺省'0'，
Here's a simplified version of my DB:
ID int(11) NOT NULL AUTO_INCREMENT,
DatePosted datetime NOT NULL,
InReplyTo int(11) NOT NULL DEFAULT '0',
很抱歉，如果这有点令人困惑，我不确定如何用不同的措词来表达.几个月以来，我一直在思考这个问题，每次解决一个问题，我都会遇到另一个问题...
Sorry if this is kind of confusing, I'm not sure how to word it any differently. I've had this problem in the back of my mind for a couple months now, and every time I solve one problem, I end up with another...
推荐答案
有很多方法.这是我喜欢的一种方法(并定期使用).
There are many ways. Here's one approach that I like (and use on a regular basis).
数据库
请考虑以下数据库结构:
Consider the following database structure:
CREATE TABLE comments (
id int(11) unsigned NOT NULL auto_increment,
parent_id int(11) unsigned default NULL,
parent_path varchar(255) NOT NULL,
comment_text varchar(255) NOT NULL,
date_posted datetime NOT NULL,
PRIMARY KEY (id)
);
您的数据将如下所示:
+-----+-------------------------------------+--------------------------+---------------+
| id | parent_id | parent_path | comment_text | date_posted |
+-----+-------------------------------------+--------------------------+---------------+
| 1 | null | / | I'm first | 1288464193 |
| 2 | 1 | /1/ | 1st Reply to I'm First | 1288464463 |
| 3 | null | / | Well I'm next | 1288464331 |
| 4 | null | / | Oh yeah, well I'm 3rd | 1288464361 |
| 5 | 3 | /3/ | reply to I'm next | 1288464566 |
| 6 | 2 | /1/2/ | this is a 2nd level reply| 1288464193 |
... and so on...
以一种可用的方式选择所有内容非常容易:
It's fairly easy to select everything in a useable way:
select id, parent_path, parent_id, comment_text, date_posted
from comments
order by parent_path, date_posted;
按parent_path, date_posted排序通常会按照生成页面时所需的顺序生成结果；但是您需要确保在注释表上有一个索引，该索引可以正确地支持此操作-否则查询就可以了，但是它确实非常低效:
ordering by parent_path, date_posted will usually produce results in the order you'll need them when you generate your page; but you'll want to be sure that you have an index on the comments table that'll properly support this -- otherwise the query works, but it's really, really inefficient:
create index comments_hier_idx on comments (parent_path, date_posted);
对于任何给定的单个注释，很容易获得该注释的整个子注释树.只需添加一个where子句:
For any given single comment, it's easy to get that comment's entire tree of child-comments. Just add a where clause:
select id, parent_path, parent_id, comment_text, date_posted
from comments
where parent_path like '/1/%'
order by parent_path, date_posted;
添加的where子句将使用我们已经定义的相同索引，因此我们很好.
the added where clause will make use of the same index we already defined, so we're good to go.
请注意，我们尚未使用parent_id.实际上，这并非绝对必要.但是我将其包含在内，因为它允许我们定义传统的外键来强制执行参照完整性，并在需要时实现级联的删除和更新.外键约束和级联规则仅在INNODB表中可用:
Notice that we haven't used the parent_id yet. In fact, it's not strictly necessary. But I include it because it allows us to define a traditional foreign key to enforce referential integrity and to implement cascading deletes and updates if we want to. Foreign key constraints and cascading rules are only available in INNODB tables:
ALTER TABLE comments ENGINE=InnoDB;
ALTER TABLE comments
ADD FOREIGN KEY ( parent_id ) REFERENCES comments
ON DELETE CASCADE
ON UPDATE CASCADE;
管理层次结构
为了使用这种方法，当然，您必须确保在插入每个注释时正确设置parent_path.而且，如果四处移动注释(这肯定是一个奇怪的用例)，则必须确保手动更新从属于移动注释的每个注释的每个parent_path. ...但是这些都是很容易掌握的事情.
In order to use this approach, of course, you'll have to make sure you set the parent_path properly when you insert each comment. And if you move comments around (which would admittedly be a strange usecase), you'll have to make sure you manually update each parent_path of each comment that is subordinate to the moved comment. ... but those are both fairly easy things to keep up with.
如果您真的想花哨(并且如果您的数据库支持它)，则可以编写触发器来透明地管理parent_path －我将为读者留个练习，但是基本思想是插入和更新触发器将在提交新的插入操作之前触发.他们会沿着树走(使用parent_id外键关系)，并相应地重建parent_path的值.
If you really want to get fancy (and if your db supports it), you can write triggers to manage the parent_path transparently -- I'll leave this an exercise for the reader, but the basic idea is that insert and update triggers would fire before a new insert is committed. they would walk up the tree (using the parent_id foreign key relationship), and rebuild the value of the parent_path accordingly.
甚至可以将parent_path分解为一个单独的表，该表完全由注释表上的触发器管理，并带有一些视图或存储过程来实现所需的各种查询.因此，完全可以将中间层代码与需要了解或关心存储层次结构信息的机制隔离开来.
It's even possible to break the parent_path out into a separate table that is managed entirely by triggers on the comments table, with a few views or stored procedures to implement the various queries you need. Thus completely isolating your middle-tier code from the need to know or care about the mechanics of storing the hierarchy info.
当然，任何花哨的东西都不是必需的-通常只需将parent_path放到表中，并在中间层中编写一些代码以确保对所有内容进行正确的管理就足够了.您已经必须管理的其他字段.
Of course, none of the fancy stuff is required by any means -- it's usually quite sufficient to just drop the parent_path into the table, and write some code in your middle-tier to ensure that it gets managed properly along with all the other fields you already have to manage.
施加限制
MySQL(和其他一些数据库)允许您使用LIMIT子句选择数据的页面":
MySQL (and some other databases) allows you to select "pages" of data using the the LIMIT clause:
SELECT * FROM mytable LIMIT 25 OFFSET 0;
不幸的是，在处理像这样的分层数据时，仅LIMIT子句无法产生预期的结果.
Unfortunately, when dealing with hierarchical data like this, the LIMIT clause alone won't yield the desired results.
-- the following will NOT work as intended
select id, parent_path, parent_id, comment_text, date_posted
from comments
order by parent_path, date_posted
LIMIT 25 OFFSET 0;
相反，我们需要在要施加限制的级别上进行单独选择，然后将其与子树"查询重新结合在一起以提供最终所需的结果.
Instead, we need to so a separate select at the level where we want to impose the limit, then we join that back together with our "sub-tree" query to give the final desired results.
类似这样的东西:
select
a.*
from
comments a join
(select id, parent_path
from comments
where parent_id is null
order by parent_path, post_date DESC
limit 25 offset 0) roots
on a.parent_path like concat(roots.parent_path,roots.id,'/%') or a.id=roots.id)
order by a.parent_path , post_date DESC;
请注意，语句limit 25 offset 0埋在内部select的中间.该语句将检索最近的25条根级别"注释.
Notice the statement limit 25 offset 0, buried in the middle of the inner select. This statement will retrieve the most recent 25 "root-level" comments.
[edit:您可能会发现自己必须花一点时间才能获得按照自己喜欢的方式订购和/或限制物品的能力.这可能包括在以parent_path编码的层次结构中添加信息.例如:您可能决定将post_date包含在parent_path:/{id}:{post_date}/{id2}:{post_date2}/{id3}:{post_date3}/中，而不是/{id}/{id2}/{id3}/.这将使获取所需的顺序和层次结构变得非常容易，而不必先填充字段并在数据更改时对其进行管理.]
[edit: you may find that you have to play with stuff a bit to get the ability to order and/or limit things exactly the way you like. this may include adding information within the hierarchy that's encoded in parent_path. for example: instead of /{id}/{id2}/{id3}/, you might decide to include the post_date as part of the parent_path: /{id}:{post_date}/{id2}:{post_date2}/{id3}:{post_date3}/. This would make it very easy to get the order and hierarchy you want, at the expense of having to populate the field up-front, and manage it as the data changes]
希望这会有所帮助. 祝你好运！
hope this helps. good luck!
这篇关于多层评论回复:显示和存储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
