Select related¶

select_related is a method that retrieves related objects in the same query. It is used to optimize the number of queries that are executed.

Let's say we have a Person model that has a company field that is a reference to the Company model, and the Company model has a location field that is a reference to the Location model.

SyncAsync

from models.user.person import Person
from models.user.company import Company
from models.location.location import Location

location = Location(name='Location 1').save()
company = Company(name='Company 1', location=location).save()
Person(name='John', company=company).save()
Person(name='Jane', company=company).save()

from models.user.person import Person
from models.user.company import Company
from models.location.location import Location

location = await Location(name='Location 1').asave()
company = await Company(name='Company 1', location=location).asave()
await Person(name='John', company=company).asave()
await Person(name='Jane', company=company).asave()

Now, if we want to get all persons and their companies, we can do it like this:

SyncAsync

for person in Person.objects.all().execute():
    print(f'{person.name} works at {person.company.name}')

for person in await Person.objects.all().aexecute():
    print(f'{person.name} works at {person.company.name}')

This will execute one query to get all persons and then for each person, it will execute a query to get the company. So if we have 100 persons, it will execute 101 queries.

To optimize this, we can use select_related:

SyncAsync

for person in Person.objects.select_related('company').all().execute():
    print(f'{person.name} works at {person.company.name}')

for person in await Person.objects.select_related('company').all().aexecute():
    print(f'{person.name} works at {person.company.name}')

This will execute only one query to get all persons and their companies.

Now let's image we want to get all persons, their companies, and the locations of the companies. We can do it like this:

SyncAsync

for person in Person.objects.select_related('company').all().execute():
    print(f'{person.name} works at {person.company.name} located at {person.company.location.name}')

for person in await Person.objects.select_related('company').all().aexecute():
    print(f'{person.name} works at {person.company.name} located at {person.company.location.name}')

This will execute one query to get all persons and their companies, and then for each person, it will execute a query to get the location of the company. So if we have 100 persons, it will still execute 101 queries.

We can optimize this by using select_related with multiple arguments:

SyncAsync

for person in Person.objects.select_related(
    'company',
    'company__location',
).all().execute():
    print(f'{person.name} works at {person.company.name} located at {person.company.location.name}')

for person in await Person.objects.select_related(
    'company',
    'company__location',
).all().aexecute():
    print(f'{person.name} works at {person.company.name} located at {person.company.location.name}')

Great! Now it will execute only one query to get all persons, their companies, and the locations of the companies.