Hacker News Reader

Comment by barosl | Hacker News Reader

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.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.

View on HN

This pattern is commonly called "materialized path", if anyone is trying to search for it.

Thanks! I wanted to know what it is called.

Thanks for the name! We do this internally to store various levels of product data in a single mongo collection (e.g., style, variant).

A 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

But beware of what may happen when you get over 9:

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.

I've done research on this and other problems, based on Drupal's solutions: https://bojanz.wordpress.com/2014/04/25/storing-hierarchical...

Or use your decimal as a place extension instead of a hierarchal separator. Your tree would look like this:

Then you just ensure that '.' sorts after all digits. (Probably by using a different symbol.) Basically, the '.' means "insert after."

In postgres specifically you can use an int array field rather than a string

PostgreSQL has a specific type for this, ltree.

Reply on news.ycombinator.com