paginating django prefetch_related queryset results in n+1 queries per row

Each row in the paginated page makes a separate query for each “child” object, instead of prefetching the child objects (n+1 queries).

model.objects.prefetch_related(Prefetch (lookup=“”, queryset=“lookup model.filter(object_id)”, to_attr=“relatedobjects”)).

There are several models involved, the main ones being models A, B, C, and D.

Models E, F, and G have a ForeignKey each to model A.

A and B have an implicit through-table: model AB with an extra field in it.

Model ABC has a ForeignKey each to A, B, and C, with a UniqueConstraint: fields=[a, b, c].

Model C has a ForeignKey to B.

The view function:

var_f = E.objects.filter(a=OuterRef(name=“id”)).order_by(“-E.DateTimeField”) # a here in OuterRef is ForeignKey to A.


queryset = (A.objects.only(“id”, “fieldX”, “fieldY”)
.filter(fieldZ=“str”, ab__b_id=someId, ab__boolField=False)
.prefetch_related(
    Prefetch(
        lookup=“related_name kwarg on field b in model ABC”,
        queryset=ABC.objects.filter(fieldA_id=someId),
        to_attr=“str”
    )
)
.annotate(
  str=F(name=“b__to_choice_field_model__field”),
  str=F(name=“b__to_another_choice_field_model__field”),
  str=Max(“some__related_datetime_field”),
  str=Subquery(
        queryset=var_f.values(“somefield”)[:1]
      ),
  str=Subquery(
        queryset=var_f.values(“someotherfield”)[:1]
  ),
)
.order_by(Func(F(name=“some_alphanum_field”, function=“alphanum”))

I needed to get one level deeper in Prefetch to get to the objects I needed. Queryset -> (prefetch_related -> Prefetch -> select_related). The clue is to ask what exactly is prefetch_related prefetching: the objects I expect, or just the mapping to the objects. The objects expected or desired have to be reached in prefetch_related or select_related, i.e., there has to be an explicit queryset asking for the objects however many levels it might take to get to them; optimally one may get to them at the second level on m2m; anything further down requires getting to that related object queryset. No more N+1 queries now. I was stumped for days on this.