Why ForeignKey reference in Django model's str method is a bad idea

#python #django

Cutting database queries by not referencing Foreign Keys. Or fixing bad behavior.

So you have a model with foreign key:

from django.db import models
from .models import Author

class Book(models.Model):
    name = models.CharField(...)
    author = models.ForeignKey(to=Author,...)

Now you want to see an author in your admin panel. Easy, right? Just add:

def __str__(self):
    return f'{self.id} {self.author.name}'

What this will do is hit our database for an author name for every row present on a page. Imagine there are 100 rows. That's 100 more hits. This problem is known as the n+1 query.

The solution is quite simple. If that's a ForeignKey, you need to use select_related on your queryset. If that's a ManyToMany field or reverse FK, then you need a prefetch_related method. And, by the way, that's the difference between those two methods. It's a common interview question.

But in our case it's not a simple query. If you really want to fix that you would need to dig deeper into how Django admin works and provide custom queryset to the view.

When I encountered this problem the solution that has been accepted was just removing the reference to ForeingKey from the __str__ method ¯\_(ツ)_/¯

A little ToDo for myself: find a way to solve this via select_related.

UPDATE after half a year

For some time I didn't face this problem but not long ago I did. So, as I said, the solution to this is quite simple. All we need to do is to redefine get_queryset method on admin model. For example:

@admin.register(models.Book)
class BookAdmin(admin.ModelAdmin):
    def get_queryset(self, request):
        qs = self.model._default_manager.select_related(
            'author',
        )
        # This stuff is marked as TODO in Django 3.1
        # so it might change in the future
        ordering = self.get_ordering(request)
        if ordering:
            qs = qs.order_by(*ordering)

        return qs

Now there will be just one query per record, containing JOIN clause, hitting our database.

Optimization FTW 😏

Top comments (5)

Constantine • Mar 28 '20

Sure, but you can access that inside the model page (e.g. /admin/model/id). Or generate a report. But throwing hundreds of queries when loading one page is really bad. I still had no time to figure out how to prefetch everything in admin view but there should be a way.

Pavel Vergeev • Mar 28 '20

You can denormalize the tables, duplicating author name on the Book instances. But only in case you actually need the data and cannot afford the overhead, of course.