从数据库中获取节点树以进行进一步渲染的最佳实践是什么? [英] What is the best practice for fetching a tree of nodes from database for further rendering?

问题描述

假设我们有一个包含用户评论的表格.一级注释引用了其所附的文章.较深层的注释在设计上没有此引用，但是有对其父注释的引用.

对于此数据库结构-提取给定文章的所有注释然后以html格式呈现的最有效方法是什么? (假设我们大约有200条第一级评论和最深的20条评论)

解决方案

我通常推荐一种称为封闭表的设计.

请参阅我对具有SQL和PHP的分层数据模型.我开发了一个PHP应用程序，该应用程序从具有490k节点的层次结构数据的集合中以0.3秒的时间渲染树.

我在此处发布了有关闭包表的博客:渲染树带有闭合表.

在我的书 SQL反模式中，我写了一章关于分层数据的不同策略:避免数据库编程的陷阱.

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)

解决方案

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: Avoiding the Pitfalls of Database Programming.

这篇关于从数据库中获取节点树以进行进一步渲染的最佳实践是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！