从数据库获取节点树以进行进一步渲染的最佳实践是什么？
假设我们有一个包含用户评论的表。一级评论引用了它们所附加的文章。更深层次的评论在设计上没有此引用，但它们有对其父评论的引用。
对于这个数据库结构 - 获取给定文章的所有评论然后以 html 格式呈现它的最有效方法是什么？ （假设我们有大约 200 条第一级评论，最深层有 20 条评论）
Let's say we have a table with user comments. First-level comments have a reference to an article they are attached to. Deeper-level comments do not have this reference by design but they have a reference to it's parent comment.
For this database structure - what would be the most efficient way to fetch all comments for a given article and then render it in html format? (Let's assume that we have approx. 200 comments of first level and the deepiest level of 20)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我通常推荐一种名为Closure Table的设计。
请参阅我对 将平面表解析为树的最有效/优雅的方法是什么？
我还设计了这个演示文稿：使用 SQL 和 PHP 的分层数据模型。我开发了一个 PHP 应用程序，可以在 0.3 秒内从具有 49 万个节点的分层数据集合中渲染出一棵树。
我在这里写了关于闭包表的博客： 使用闭包表渲染树。
我在我的书中写了一章关于分层数据的不同策略，SQL Antipatterns Volume 1：避免数据库编程的陷阱。
I usually recommend a design called Closure Table.
See example in my answer to What is the most efficient/elegant way to parse a flat table into a tree?
I also designed this presentation: Models for Hierarchical Data with SQL and PHP. I developed a PHP app that render a tree in 0.3 seconds, from a collection of hierarchical data with 490k nodes.
I blogged about Closure Table here: Rendering Trees with Closure Table.
I wrote a chapter about different strategies for hierarchical data in my book, SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming.
对于最有效的方式，Quassnoi 就这个主题撰写了一系列文章。
我建议你阅读第一篇文章并调整示例以适用于您的特定表，但关键是创建一个可以递归您需要获取的行的函数。您可能还需要级别（层次结构的深度），因此第二篇文章可能也相关。
如果您需要对数据进行其他类型的查询，其他文章可能会很有用。他还有一篇文章 邻接列表与嵌套集：MySQL，他在其中比较了邻接模型和嵌套集模型的高度优化查询。
For the most efficient way Quassnoi has written a series of articles on this subject.
I suggest you read the first article and adapt the examples to work with your specific table, but the crux is to make a function that can recurse over the rows you need to fetch. You probably also want the level (depth in the heirarchy) so the second article is probably also relevant too.
The other articles may be useful if you need to make other types of queries on your data. He also has an article Adjacency list vs. nested sets: MySQL in which he compares highly optimized queries for both the adjacency model and the nested set model.