2 ms·
This is cool - I hadn't seen the materialized view + CTE trick before, or the ltree extension. I've implemented this in the past using the classic adjacency li
by simonw 5y ago
This is cool - I hadn't seen the materialized view + CTE trick before, or the ltree extension.
I've implemented this in the past using the classic adjacency list, materialized path or nested sets mechanisms - all by using Django Treebeard, which offers a very robust implementation of all three of them: https://django-treebeard.readthedocs.io/en/latest/ https://django-treebeard.readthedocs.io/en/latest/
- mceachen 5y agoI'm actually using materialized views and CTE with PhotoStructure (where hierarchies are arguably the cornerstone of the product). It's pretty funny that I landed on this implementation, given that I spent a couple years building https://github.com/ClosureTree/closure_tree https://github.com/ClosureTree/closure_tree (one of the most popular acts-as-hierarchy ActiveRecord gems), as it (unsurprisingly) uses closure trees. When CTE isn't available, closure trees are nice, but boy howdy, does that closure tree table get gigantic with deeper graphs. If CTE is available, closure trees don't even come close in performance and simplicity. (Hint: materialized paths should use a unique separator: ASCII 0x1F is applicable: https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_separators https://en.wikipedia.org/wiki/C0_and_C1_control_codes#Field_...)