I have the following Django template code with an N+1 query:

{% for theobject in objs %}
  {% for part in theobject.parts_ordered %}
      <li>{{ part }}</li>
  {% endfor %}
{% endfor %}

Here is parts_ordered on TheObject:

  class TheObject:
    # ...
    def parts_ordered(self) -> list["Part"]:
        return self.parts.all().order_by("pk")

And here is the Part object:

  class Part:
    # ...
    theobject = models.ForeignKey(
        TheObject, on_delete=models.CASCADE, related_name="parts"
    )

and here is the prefetch getting objs:

    ofs = ObjectFormSet(
        queryset=TheObject.objects
        .filter(objectset=os)
        .prefetch_related("parts")
    )

I think the order_by("pk") disrupts the prefetch.

This is what chatgpt recommends, and it works (no more N+1 queries, results seem the same):

  class TheObject:
    # ...
    def parts_ordered(self) -> list["Part"]:
        if (
            hasattr(self, "_prefetched_objects_cache")
            and "parts" in self._prefetched_objects_cache
        ):
            # Use prefetched data and sort in Python
            return sorted(
                self._prefetched_objects_cache["parts"], key=lambda cc: cc.pk
            )

        # Fallback to querying the DB if prefetching wasn’t used
        return self.parts.all().order_by("pk")

Should I rely on _prefetched_objects_cache? Is there a better way?

I have the following Django template code with an N+1 query:

{% for theobject in objs %}
  {% for part in theobject.parts_ordered %}
      <li>{{ part }}</li>
  {% endfor %}
{% endfor %}

Here is parts_ordered on TheObject:

  class TheObject:
    # ...
    def parts_ordered(self) -> list["Part"]:
        return self.parts.all().order_by("pk")

And here is the Part object:

  class Part:
    # ...
    theobject = models.ForeignKey(
        TheObject, on_delete=models.CASCADE, related_name="parts"
    )

and here is the prefetch getting objs:

    ofs = ObjectFormSet(
        queryset=TheObject.objects
        .filter(objectset=os)
        .prefetch_related("parts")
    )

I think the order_by("pk") disrupts the prefetch.

This is what chatgpt recommends, and it works (no more N+1 queries, results seem the same):

  class TheObject:
    # ...
    def parts_ordered(self) -> list["Part"]:
        if (
            hasattr(self, "_prefetched_objects_cache")
            and "parts" in self._prefetched_objects_cache
        ):
            # Use prefetched data and sort in Python
            return sorted(
                self._prefetched_objects_cache["parts"], key=lambda cc: cc.pk
            )

        # Fallback to querying the DB if prefetching wasn’t used
        return self.parts.all().order_by("pk")

Should I rely on _prefetched_objects_cache? Is there a better way?

• django
• django-prefetch-related

1 Answer 1

I wouldn't use private methods, or attributes in your case. As a rule, library authors mark them as non-public for a reason. Most often they are implementation details that may change in some next version, which may cause your code to stop working.

In your case, you can make things simpler, but still solve the N + 1 problem. For this you can use Prefetch object and this query:

from django.db import models  
  
  
ofs = ObjectFormSet(  
    queryset=TheObject.objects  
    .filter(objectset=os)  
    .prefetch_related(  
        models.Prefetch(  
            lookup='parts',  
            queryset=Part.objects.order_by('pk'),  
            to_attr='parts_ordered',  
        ),  
    )  
)

This will give similar results to yours and should improve overall performance a bit, since the database is doing the sorting and not python.

UPDATED

To answer your question from the comment - you just need to remove the parts_ordered method, I meant that the queryset in my answer is its replacement. You can also use any other attr name for the to_attr argument, for example:

ofs = ObjectFormSet(  
    queryset=TheObject.objects  
    .filter(objectset=os)  
    .prefetch_related(  
        models.Prefetch(  
            lookup='parts',  
            queryset=Part.objects.order_by('pk'),  
            to_attr='ordered_parts',  
        ),  
    )  
)

And then in your template you should use it this way:

{% for theobject in objs %}
  {% for part in theobject.ordered_parts %}
      <li>{{ part }}</li>
  {% endfor %}
{% endfor %}

For more flexible use, if this query can be used in different places. You can define this as a method for your manager. It would look something like this:

from django.db import models  
  
  
class TheObjQuerySet(models.QuerySet):  
    def attach_ordered_parts(self):  
        return self.prefetch_related(  
            models.Prefetch(  
                lookup='parts',  
                queryset=Part.objects.order_by('pk'),  
                to_attr='ordered_parts',  
            )  
        )  
  
  
class TheObject(models.Model):  
    # ...  
    objects = TheObjQuerySet.as_manager()


ofs = ObjectFormSet(  
    queryset=TheObject.objects  
    .filter(objectset=os)  
    .attach_ordered_parts()
)

And accordingly the code in your template:

{% for theobject in objs %}
  {% for part in theobject.ordered_parts %}
      <li>{{ part }}</li>
  {% endfor %}
{% endfor %}