How to recursively query in django efficiently?
<p>I have a model, which looks like:</p>

<pre><code>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')
</code></pre>

<p>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.</p>

<p>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:</p>

<pre><code>def get_team(self):
    team = [self]
    for c in self.team_members.all():
        team += list(c.get_team())
        if len(team) &gt; 2000:
            break
    return team
</code></pre>

<p>I get the team members of a member by:</p>

<pre><code>member = StaffMember.objects.get(pk=72)
team = member.get_team()
</code></pre>

<p>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? </p>

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

<pre><code>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
''')
</code></pre>

<p>References:
<a href="https://docs.djangoproject.com/en/1.10/topics/db/sql/" rel="nofollow">Raw SQL queries in Django</a><br>
<a href="https://www.postgresql.org/docs/current/static/queries-with.html" rel="nofollow">Recursive Common Table Expressions in PostgreSQL</a></p>

python django recursion list-comprehension django-orm