3 ms·
Does anyone remember how Drupal handles the hierarchical comments? It maintains a "sort key" field for each comment, which consists of multiple index numbers fo
by barosl 12y ago
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.
- twerquie 12y agoThis pattern is commonly called "materialized path", if anyone is trying to search for it.
- barosl 12y agoThanks! I wanted to know what it is called.
- vkjv 12y agoThanks for the name! We do this internally to store various levels of product data in a single mongo collection (e.g., style, variant).
- benjohnson 12y agoA similar system is also heavily used in the construction world in their "Work Breakdown Structure" - a borrowing on how they document airplane parts that are part of an assembly, that are part of a module, that part of a structure. http://en.wikipedia.org/wiki/Work_breakdown_structure http://en.wikipedia.org/wiki/Work_breakdown_structure
- Someone 12y agoBut beware of what may happen when you get over 9: 1 1.1 1.2 10 2 2.1 To support that, you will have to be careful when picking collation order, and binary collation will not do it. It may be better to use fixed-width numbers with leading zeroes instead, but that, of course, may unnecessarily grow the space used by the table. And of course, that also is what RCS and SCCS use for numbering revisions.
- bojanz 12y agoI've done research on this and other problems, based on Drupal's solutions: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical-data-materialized-path/ https://bojanz.wordpress.com/2014/04/25/storing-hierarchical...
- Retra 12y agoOr use your decimal as a place extension instead of a hierarchal separator. Your tree would look like this: 1 11 12 1.0 2 21 Then you just ensure that '.' sorts after all digits. (Probably by using a different symbol.) Basically, the '.' means "insert after."
- ketralnis 12y agoIn postgres specifically you can use an int array field rather than a string
- jeltz 12y agoPostgreSQL has a specific type for this, ltree.