Use prefetch_related conveniently with Django

The number of Django projects in the company is increasing, and the number of uncool queries is likely to increase, so I will leave a note easily.

Use prefetch_related because it would be disastrous to skip the query every time you loop when fetching a foreign key backreference or getting a Many-to-Many reference without thinking about it using an ORM. The story of melting

If it is a normal Foreign Key, you can pull it by the required hierarchy with select_related.

Use normally

For the time being, such a model

from django.db import models

class Campaign(models.Model):
    name = models.CharField(max_length=255)
    created_at = models.DateTimeField()

class Creative(models.Model):
    name = models.CharField(max_length=255, default="")
    campaign = models.ForeignKey(Campaign)
    created_at = models.DateTimeField()

If you want to subtract the Campaign from Creative, you can use select_related to join the Campaign without selecting it every time you loop.

for creative in Creative.objects.all().select_related():
	print(creative.campaign)

Use prefetch_related to pull a list of Creatives from a Campaign

for campaign in Campaign.objects.all().prefetch_related("creative_set"):
	print(campaign.creative_set.all())

It seems that all Campaigns are pulled once instead of join, and the list of campaign.id is pulled where in. Well, it's better than flying every time, and in most cases it doesn't seem to be a problem.

By the way, narrowing down the campaign with creative is usually done with a query

Campaign.object.filter(creative_set__name="aaa")

Use Prefetch object

If you simply use prefetch_related, you can only get the thing of XXXX_set.all (). If you narrow down the creative_set, the query will fly every time you loop, which makes you very sad.

for campaign in Campaign.objects.all().prefetch_related("creative_set"):
	print(campaign.creative_set.filter(name__startswith="hoge"))

Therefore, if you want to narrow down the destination of reverse reference or Many-to-Many by using a filter, or if you want to specify the order, use the Prefetch object included from 1.7 of Django. Thanks to this guy, I feel that Django's ORM has improved a lot.

from django.db import models
from django.db.models import Prefetch

for campaign in Campaign.objects.all().prefetch_related(Prefetch("creative_set", queryset=Creative.objects.filter(name__startswith="hoge").order_by("-created_at"), to_attr="creatives")):
	if len(campaign.creatives) > 0:
        print(campaign.id, creatives[0].id)

With this, no matter how many Campaigns you have, you only have to skip the query twice.

View query

When tuning a query, I think you'll be using the Django Shell to check the query, so it's a good idea to tweak the logger so that the SQL statement is displayed when you hit the query in the shell. I think I'm messing with the log settings if I'm doing it normally, but I'm happy to set the loglevel in django.db.backends to DEBUG only when investigating queries.

settings.py

LOGGING = {
    'version': 1,
    'handlers': {
        'console': {
            'level': 'DEBUG',
            'class': 'logging.StreamHandler',
        }
    },
    'loggers': {
        'django.db.backends': {
            'level': 'DEBUG',
            'handlers': ['console'],
        },
    }
}

For tuning by view, it is basic to check the query using django-debug-toolbar.