I have a model Department which has Roles which Users are linked to. I know the user, but need to get the user.role.department_set.all() (all departments the user's role belong to) and then get all the roles in each of those departments.

Models:

class User(AbtractUser):
    role = models.ForeignKey(Role)

class Role(models.Model):
    name = models.CharField()

class Department(models.Model):
    name = models.CharField()
    roles = models.ManyToManyField()

How can I get all the departments which contains the user's role, then get the roles of all those departments?

I am doing something like this currently but it is a very expensive lookup:

    def get_departments(self):
        """Returns a list of departments the user is in."""
        return self.role.department_set.all()

    def get_users_departments_roles(self):
        """Returns all the roles of all the departments the user is in."""
        departments = self.get_departments()
        roles = Role.objects.none()
        for department in departments.iterator():
            for role in department.roles.all():
                roles |= Role.objects.filter(name=role.name)
        return roles

Side question: After typing all this out, would it be better to just add the a department field to each user? My concern is that if a role changes, I will also have to change the department at the same time and it will add complexity.

CodePudding user response：

You can filter a Role queryset and follow the ManyToManyField relationship backwards using the name department

You can pass a queryset to the __in filter to produce a nested query

    def get_users_departments_roles(self):
        """Returns all the roles of all the departments the user is in."""
        departments = self.get_departments()
        return Role.objects.filter(department__in=departments).distinct()

Note however that certain databases (MySQL) don't perform nested queries very well, see this warning at the end of this section in the docs