Django ORM: misleading `first()` for `prefetch_related`

Question

Working on a DRF endpoint I expierenced an issue with prefetch_related and first called on the prefetched set. Let's consider two models: X and Y; Y contains a foreign key to X.

Then I perform following code:

qs = X.objects.all().prefetch_related("y_set")

for x in qs:
    for y in x.y_set.all():
        print(e)

Everything is OK, django performs 2 queries as expected.

Then I perform:

for x in qs:
    for y in x.y_set.all():
        print(e)
    first = x.y_set.first()

In this example, Django perform n+2 queries what is not expected (at least for me).

I found a workaround with:

for x in qs:
    for y in x.y_set.all():
        print(e)
    first = y_set.all()[0] if y_set.all() else None

but it's not satisfying for me - I feel like checking if qs is not empty and then taking the first element is a bit messy, I'd definitely prefer to use first or some other function that hides this logic.

Can anyone explain why first doesn't use prefetched cache or can you give me a tip how to handle it a bit clearer? (I don't want to add a wrapper to handle that, I prefer native django orm solution. Also I can't just take the first element from the loop - I simplified the example a lot)

Answer 1

.first() basically uses the LIMIT clause in SQL to get the first object of a query. So when one calls queryset.first() it naturally makes a separate query.

You further ask that since the queryset is already present in memory why doesn't .first() simply use that evaluate queryset? Well let me put it this way:

It is quite common to chain methods on a queryset .annotate(...).filter(...) etc. and we can do things like:

queryset = SomeModel.objects.all()
for object in queryset:
    print(object)
queryset2 = list(queryset.filter(a=1))

Here we expect queryset2 to make a different query to the database instead of perhaps filtering the objects at the python level because for some reason the database itself might have new entries or perhaps we may even be making some annotations instead of the simply calling .filter() so we want this to be a separate query. This in essence is the same reason .first() will not simply use the prefetched objects.