Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
328 views
in Technique[技术] by (71.8m points)

python - How to recursively query in django efficiently?

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...