Community Portal

Properly embed adjacency lists

Created on Saturday 9 October 2021, 15:16

Back to task list
  • ID
  • Project
    Metabolism of Cities Data Hub
  • Status
  • Priority
  • Type
    Programming work
  • Tags
    Data Hub Priority Plan 2021 General data hub improvements
  • Assigned to
    No one yet
  • Subscribers
    Paul Hoekman

You are not logged in

Log In Register

Please join us and let's build things, together!


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.

Discussion and updates

New task was created

Task was assigned to Paul Hoekman

Status change: Open → In Progress

Just FYI I've been working on this for the past week, in a separate branch called adjacencylists which I will merge into master when it's sufficiently completed. I'm just adding this task to keep a log of this work and notify people when it's completed.

I am stepping down from my roles at MoC but if another volunteer wants to take this forward I am happy to go over what I've done so far.

Paul Hoekman is no longer responsible for this task