Get parent by its children in Django Queryset

Question

I have this model:

Department(models.Model):
    title =  models.CharField(max_length=128)
    reference = models.ForeignKey('self', related_name='reference')

As you see, each object of this model can have parent and conversely.

For example, these are department's objects:

id = 1 , title = 'dep 1' , reference_id = Null
id = 2 , title = 'dep 2' , reference_id = 1
id = 3 , title = 'dep 3' , reference_id = Null
id = 4 , title = 'dep 4' , reference_id = 1
id = 5 , title = 'dep 5' , reference_id = 3
id = 6 , tiltl = 'dep 6' , reference_id = 3
id = 7 , title = 'dep 7' , reference_id = 3
id = 8 , title = 'dep 8' , reference_id = 9
id = 9 , title = 'dep 9' , reference_id = Null
id = 10, title = 'dep 10', reference_id = 9

Now I want to get list of objects that has this condition: Each parent by its children. like this:

- dep 1 
    dep 2 
    dep 4 
- dep 3 
    dep 5
    dep 6
    dep 7
- dep 9
    dep 8 
    dep 10

How can I do that? (Can I create this list just with Django ORM?)

Update This is my department levels:

Department_Level = (
    (1, 'headquarters'),
    (2, 'unit'),
    (3, 'center'),
)
level = models.IntegerField(choices=Department_Level,default=.Department_Level[2][0])    

And I want just level 2 and 3 in my list.

Answer 1

It may be works using ArrayAgg. Please try it.

from django.contrib.postgres.aggregates import ArrayAgg


# dept_id is root department id
dept_list = Department.objects.filter(id=dept_id).annotate(reference_list=ArrayAgg('department__reference'))

Answer 2

First, get all objects

mapper = dict(Department.objects.values_list("id", "reference_id"))

This will result in a dict (child to parent)

{1: None, 2: 1, ...}

Convert it dict with parent to children

res = dict()
for child, parent in mapper.items():
     res.setdefault(parent, []).append(child)

Now you can construct a data structure over it however you like. You can also get the grandchildren by finding the children of all children of an item.

Best to construct a tree structure and write a class method which returns the tree with res as input.

Edit. To answer the question posted in comments to with regards to N levels, we can use a tree-structure instead of a normal dict, but we can still fetch all grand children upto any level with the current dict structure. Below code with the assumption that there are no circular relationships.

def get_all_grand_children(node_id, n_level) -> List:
    if n_level == 0:
         return []
    output = list()
    for child in res.get(node_id, []):
         output.append(child)
         output.extend(get_all_grand_children(child, n_level - 1)
    return output