The nested set model – hierarchical data in MySQL
I’m working on a small PHP project handling data which is organized in a hierarchical fashion. This is a common task and I have done it before creating forums and the like. This time no different than any other I was swearing over the rather ugly and most of all inefficient code needed for fetching a full hierarchy from the database and displaying it nicely. Vaguely I remembered reading something about a rather different approach.
Searching Google I finally found a MySQL article on the nested set model which is a lot more efficient. The algorigthm basically skips the whole parent/child relationship between nodes. Instead you work with what is best described as “neighbouring” nodes. This allows you to retrieve a fully ordered hierarchical tree, including the level/depth of each node in the tree, using only one simple sql statement.
Those that are not familiar with the nested set model should definitely read the article.
If you enjoyed this post, please consider to leave a comment or subscribe to the feed and get future articles delivered to your feed reader.
Hi !
You can see : nstree by Rolf Brugger. Doctrine or Propel have some functionnalities to handle this kind of data structure.