DJANGO: Get a parent queryset from a child queryset

Question

Say I have the following for my models.py:

from django.db import models

class Department(models.model):
    name = models.CharField(max_length=255)

class User(models.model):
    name = models.CharField(max_length=255)
    department = models.ForeignKey(
        Department, related_name="department_users", on_delete=models.CASCADE
    )
    ...

So I am doing a lot of complex filtering on my User queryset to get to a subset of Users who are considered "active" but now I need to generate a list of Departments who have at least one "active" user.

I know that I can use a reverse lookup like: Department.objects.filter(department_users__name__contains=...) etc and do the filtering all over again but I wanted to know if there was a way to get that information directly from the "active" User queryset.

I tried using .values() but that gives me a queryset of dictionaries and I really wanted a queryset of django Models so I can do further filtering for the Department queryset.

Do you guys know if there is a way to do that?

Answer 1

If you have a queryset of Users, you can load the users and query the departments with:

Department.objects.filter(department_users__in=list(queryset_of_users)).distinct()

you can also do this without materializing the list of users with:

Department.objects.filter(department_users__in=queryset_of_users).distinct()

here your queryset_of_users will be a subquery, but that means that the database will do the work to determine the active users a second time.

The .distinct() clause will prevent returning the Department as many times as there are Users in the queryset_of_users for that department.