What is the best practice for fetching a tree of nodes from database for further rendering?

Question

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)

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

Answer 2

For the most efficient way Quassnoi has written a series of articles on this subject.

• Hierarchical queries in MySQL
• Hierarchical queries in MySQL: adding level
• Hierarchical queries in MySQL: adding ancestry chains.
• Hierarchical queries in MySQL: finding leaves
• Hierarchical queries in MySQL: finding loops

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.