11–20 of 60 posts
http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-...
He spends a chapter on each of the models outlined in this post: adjacency, path, and nested set models.
Does anyone remember how Drupal handles the hierarchical comments? It maintains a "sort key" field for each comment, which consists of multiple index numbers for all levels, much like the section numbers in Wikipedia. 1 1.1 1.2 2 2.1 2.1.1 2.1.2 2.1.2.1 In this way, displaying comments in a tree form is trivial. Just ORDER BY the sort key. I find it brilliant for applying to such an application.
Does anyone remember how Drupal handles the hierarchical comments? It maintains a "sort key" field for each comment, which consists of multiple index numbers for all levels, much like the section numbers in Wikipedia. 1 1.1 1.2 2 2.1 2.1.1 2.1.2 2.1.2.1 In this way, displaying comments in a tree form is trivial. Just ORDER BY the sort key. I find it brilliant for applying to such an application.
This pattern is commonly called "materialized path", if anyone is trying to search for it.
Does anyone remember how Drupal handles the hierarchical comments? It maintains a "sort key" field for each comment, which consists of multiple index numbers for all levels, much like the section numbers in Wikipedia. 1 1.1 1.2 2 2.1 2.1.1 2.1.2 2.1.2.1 In this way, displaying comments in a tree form is trivial. Just ORDER BY the sort key. I find it brilliant for applying to such an application.
This pattern is commonly called "materialized path", if anyone is trying to search for it.
A good book on the subject is Joe Celko's Trees and Hierarchies in SQL for Smarties. http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-... He spends a chapter on each of the models outlined in this post: adjacency, path, and nested set models.
There is also ltree http://www.postgresql.org/docs/9.3/static/ltree.html Adjacency lists also don't perform that badly with recursive queries in my experience.
The project I'm on used materialized paths, which lead to great pain. I investigated nested intervals ... and they could't achieve the tree depths we needed (we were modeling a file system tree).
We are back to adjacency lists (using a parent ID) but redesigned to avoid the need for recursive ancestor and descendant queries.
_But_ the RDBMS doesn't support recursive queries and I've been curious about PostgreSQL's recursive query support. I played with it, but not on a fully loaded database with deep trees of data.
Does PostgreSQL recursive query support work well with deep trees (> 100 levels) on tables with tens of millions or more rows?
There is also ltree http://www.postgresql.org/docs/9.3/static/ltree.html Adjacency lists also don't perform that badly with recursive queries in my experience.
http://illuminatedcomputing.com/posts/2014/09/postgres-cte-f...
The problem I was tackling was making the tree sorted like you see in threaded, scored comments.
A good book on the subject is Joe Celko's Trees and Hierarchies in SQL for Smarties. http://www.amazon.com/Hierarchies-Smarties-Edition-Kaufmann-... He spends a chapter on each of the models outlined in this post: adjacency, path, and nested set models.
I've done nested set before; it's interesting - very fast for queries, but requires a lengthy insert/update cost. Also, team members were absolutely clueless as to what was really going on. I'm not sure nested sets are really much faster than what modern rdbms's can provide today.
I worked on a multi-tenant application with distinct trees present in one table and with one tree per table and so on. Fun fun fun!