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

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

Photo by israel palacio on Unsplash

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 😏

Comments

[Constantine]

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]

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.

[Constantine]

Fair point. For some reason I started to come across on a lot of DB denormalization advises quite often lately. And wondering if that's a trend now?

[Max Ong Zong Bao]

Hmmm... why would you have many to many field? I smell like bad database design if there is a many to many field in the database table.

[Constantine]

lolwhat