CODEWITHSUNDEEP
pythondjangodjango-treebeard
thebjorn
thebjorn

Reputation: 27351

How to get subordinates, recursively?

I have Django models for Organizational Units (OrgUnit) which can be company, department, team, etc., ie. it is a tree structure:

from django.db import models
from django.contrib.auth.models import User
from treebeard.mp_tree import MP_Node, MP_NodeManager

class OrgUnit(MP_Node):  # MP_Node adds a .path field
    name = models.CharField(max_length=120)

Employees can be connected to one or more OrgUnits:

class OUEmployee(models.Model):
    user = models.ForeignKey(User, related_name='employers', on_delete=models.CASCADE)
    orgunit = models.ForeignKey(OrgUnit, on_delete=models.CASCADE)

..and managers can manage several OrgUnits:

class OUManager(models.Model):
    user = models.ForeignKey(User, related_name='+', on_delete=models.CASCADE)
    manages = models.ManyToManyField(OrgUnit, blank=True)

Now I want a queryset of all employees that are subordinates of a given manager, implemented as a method on OUManager, however, the current implementation only gives the employees connected directly to the OrgUnits the manager manages, not to the child-orgunits:

    def subordinates(self):
        return OUEmployee.objects.filter(
            orgunit__in=self.manages.all()
        )

i.e. given:

root = OrgUnit.add_root(name='Acme Corporation')
dept = root.add_child(name='Dept. of Anvils and Tunnels')
team = dept.add_child(name='QA')
emp1 = OUEmployee(user=User.objects.create_user(username='emp1'),
                  orgunit=team)
emp2 = OUEmployee(user=User.objects.create_user(username='emp2'),
                  orgunit=dept)
mngr = OUManager(user=User.objects.create_user(username='manager'))
mngr.manages.add(dept)

I would like

employees = mngr.subordinates()

to return a queryset with both emp1 and emp2 (the above implementation only return emp2).

Is there a way to write subordinates so it only requires one db-hit?

The best method I have come up with so far needs to materialize all the paths first:

from django.db.models.query_utils import Q
...

    def subordinates(self):
        paths = Q()
        for p in self.manages.all().values_list('path', flat=True):
            paths |= Q(orgunit__path__startswith=p)
        return OUEmployee.objects.filter(paths)

Are there any direct ways to achieve this in one db-hit, either directly or by changing the models?

Upvotes: 1

Views: 125

Answers (0)

Related Questions