python - How to recursively query in django efficiently?

Question

I have a model, which looks like:

class StaffMember(models.Model):

    id = models.OneToOneField(to=User, unique=True, primary_key=True, related_name='staff_member')
    supervisor = models.ForeignKey(to='self', null=True, blank=True, related_name='team_members')

My current hierarchy of team is designed in such a way that there is let's say an Admin (who is at the top most point of hierarchy). Now, let's say 3 people (A, B, C) report to Admin and each one of A, B and C have their own team reporting to them and so on.

I want to find all the team members (boiling down to the bottom most level of hierarchy), for any employee. My current method to get all the team members of a person is like:

def get_team(self):
    team = [self]
    for c in self.team_members.all():
        team += list(c.get_team())
        if len(team) > 2000:
            break
    return team

I get the team members of a member by:

member = StaffMember.objects.get(pk=72)
team = member.get_team()

But obviously, this leads to a lot of db calls and my API eventually times out. What could be more efficient way to fetch all the members of a team?

Answer 1

If you're using a database that supports recursive common table expressions (e.g. PostgreSQL), this is precisely the use-case.

team = StaffMember.objects.raw('''
    WITH RECURSIVE team(id, supervisor) AS (
          SELECT id, supervisor 
          FROM staff_member
          WHERE id = 42
        UNION ALL
          SELECT sm.id, sm.supervisor
          FROM staff_member AS sm, team AS t
          WHERE sm.id = t.supervisor
        )
    SELECT * FROM team
''')

References: Raw SQL queries in Django
Recursive Common Table Expressions in PostgreSQL