Geek Answers Handbook

What is the best practice for getting a node tree from a database for further rendering?

Let's say we have a table with user comments. The first level comments contain a link to the article to which they are attached. Deeper comments do not have this design link, but they do have a link to the parent comment.

For this database structure - what would be the most efficient way to get all the comments for this article and then display it in html format? (Assume we have approximately 200 first level comments and the deepest level 20)

+6
mysql hierarchical-data
source share
2 answers

I usually recommend a design called a closing table .

See an example in my answer to What is the most efficient / elegant way to parse a flat table in a tree?

I also developed this presentation: Models for hierarchical data with SQL and PHP . I developed a PHP application that displays a tree in 0.3 seconds from a collection of hierarchical data with 490 thousand nodes.

I wrote about closing a table here: Rendering trees with a closing table .

I wrote a chapter on the various strategies for hierarchical data in my book, SQL Antipatterns: Avoiding Database Programming Errors .

+9
source share

For Quassnoi’s most effective way, a series of articles have been written on this subject.

I suggest you read the first article and adapt the examples to work with your specific table, but the main thing is to make a function that can overwrite the rows needed for extraction. You probably also need a level (depth in the hierarchy), so the second article is probably also relevant.

Other articles may be useful if you need to make other types of queries in your data. He also has an article, Adjacency List vs. Nested Sets: MySQL , in which he compares highly optimized queries for both an adjacency model and a nested set model.

+5
source share

More articles:


All Articles