Description

Our database structure employs adjacency lists to store hierarchical data. That mostly concerns the material lists and the activities lists. However, this is currently only set up in the most basic way: we have a parent field that indicates where a material or activity fits within the hierarchical tree. We do NOT yet have set up any good systems to select descendants or ancestors (so it is quite a pain to select what the parent elements are of something e.g. 5 levels deep into the tree). It is also not yet possible for that same reason to select e.g. all data that is BIOMASS... if it is not selected at the top level. But that is exactly what we need! We need to be able to select all biomass data, whether it be bananas or rice. So taking the adjacency lists to the next level is key.

On this blog there is a whole series around implementing adjacency lists in Django/PostgreSQL. It includes creating dynamic materialized paths and storing them in a recursive view, to be queried when the descendants/ancestors need to be queried. It also includes obtaining the entire tree with children as a neat json object, which will be very useful when creating a browsable/searchable tree using the fancytree plugin.