Django doesn't respect Prefetch filters in annotate

Question

class Subject(models.Model):
    ...
    students = models.ManyToMany('Student')
    type = models.CharField(max_length=100)

class Student(models.Model):
    class = models.IntergerField()
    dropped = models.BooleanField()
    ...

subjects_with_dropouts = (
    Subject.objects.filter(category=Subject.STEM).
        prefetch_related(
            Prefetch('students', queryset=Students.objects.filter(class=2020))
            .annotate(dropped_out=Case(
                        When(
                            students__dropped=True,
                            then=True,
                        ),
                        output_field=BooleanField(),
                        default=False,
                    ))
            .filter(dropped_out=True)
)

I am trying to get all Subjects from category STEM, that have dropouts of class 2020, but for some reason I get Subjects that have dropouts from other classes as well.

I know that I can achive with

subjects_with_dropouts = Subject.objects.filter(
    category=Subject.STEM,
    students__dropped=True,
    students__class=2020,
)

But why 1st approach doesn't work? I am using PostgreSQL.

Answer 1

When using prefetch, the joining is done in python. A good way to think of this is that you have two tables in the first query. One of subjects with at least one student who dropped out (note that you are doing an aggregate there (Case) so there is a JOIN with a GROUP BY on student.id), and one of students in class of 2020 (this is separate than the join in the first table). The prefetch just says to join these two separate queries using the through table that contains both of their ids representing a connection that is auto generated by ManyToManyField.

A good way to see what is actually happening is by using print(QuerySet.query) where QuerySet is the instance of the QuerySet (Subject.objects.all()). Or if you have the means, django debug toolbar is a fantastic tool that shows you the EXPLAIN statement of each query in each endpoint.