Django prefetch and select related

Question

I'm having troubles to understand prefetch_related and select_related in Django ORM. I have the following models:

class City(models.Model):
    name = models.CharField(max_length=35)
    state = models.ForeignKey('states.State', on_delete=models.CASCADE)

class Street(models.Model):
    name = models.CharField(max_length=35)
    building = models.ForeignKey('buildings.Building', on_delete=models.CASCADE)
    city = models.ForeignKey('cities.City', on_delete=models.CASCADE)

And now my views.py:

cities = City.objects.all()
streets = Street.objects.all()

for city in cities: 
    has_bank = streets.filter(building_id=1, city=city)
    if has_bank:
        city.has_bank = 1

    has_cinema = streets.filter(building_id=2, city=city)
    if has_cinema:
        city.has_cinema = 1

    has_church = streets.filter(building_id=3, city=city)
    if has_church:
        city.has_church = 1

But now it hits the database 3 times in each time the for loop iterates. I'm trying to improve the time complexity - which is now 3N + 2 where N is number of cities, but I can't understand the select_related and prefetch_related.

Can you give me an example how would I improve this so it does not hit the database 3 times in for loop?

Does this answer your question? [What's the difference between select\_related and prefetch\_related in Django ORM?](https://stackoverflow.com/questions/31237042/whats-the-difference-between-select-related-and-prefetch-related-in-django-orm) — Mohammad Ashraful Islam, Jan 23 '20 at 16:10
I know what's the difference, but I can't get it to work with `streets.filter()` I thought it will "query" the QuerySet but it does not. — popcorn, Jan 23 '20 at 16:12
@AshrafulIslam But how do I apply it for the `.filter()`. I would really appreciate a simple example. — popcorn, Jan 23 '20 at 16:16
For example `streets = Street.objects.all().select_related('building')` — Charnel, Jan 23 '20 at 16:20
`streets = Street.objects.filter(anything here...).select_related('building')` — Mohammad Ashraful Islam, Jan 23 '20 at 16:22

neverwalkaloner · Accepted Answer · 2020-01-23T17:40:03.147

3

In your specific case I suppose better to use annotation instead of prefetch:

from django.db.models import Count, Q

cities = City.objects
.annotate(bank_count=Count("street", filter=Q(street__building_id=1)))
.annotate(cinema_count=Count("street", filter=Q(street__building_id=2)))
.annotate(church_count=Count("street", filter=Q(street__building_id=3)))

Now you can directly use bank_count, cinema_count and church_count attributes:

for city in cities: 
   print(city.bank_count)
   print(city.cinema_count)
   print(city.church_count)

In case you want to use prefetch_related you need to use Prefetch object. This allows you tof filter prefetched objects:

City.objects.prefect_related(
    Prefetch("street_set", queryset=Street.objects.filter(building_id=1), to_attr='has_bank'),
    Prefetch("street_set", queryset=Street.objects.filter(building_id=2), to_attr='has_cinema'),
    Prefetch("street_set", queryset=Street.objects.filter(building_id=3), to_attr='has_church')
)

Note to_attr argument this helps you to prefetch same model's objects with different filters to different attributes. So you can do now:

for city in cities: 
   print(city.has_bank)
   print(city.has_cinema)
   print(city.has_church)

edited Jan 23 '20 at 17:40

answered Jan 23 '20 at 16:23

neverwalkaloner

46,181
7
92
100

I just saw, I missed one parameter in for loop queries - edited post. Is it still better to use annotation? I would still like to understand the `prefetch_related` and `select_related` – popcorn Jan 23 '20 at 16:29
@popcorn yes I would say it still better. Annotation will give exactly what you need in updated question. Well, prefetch and select_related also. But select related doesn't wark ib this case since it could prefecth only single object not multiple objects. As for prefetch this will work in your situation, but each prefetch_related call make additional DB query. So in your case you need to prefetch 3 queryset it will give you 3 additional DB calls. As for annotation it's only one query. And it give you requred data. – neverwalkaloner Jan 23 '20 at 16:32
Probably this links could be helpful https://docs.djangoproject.com/en/3.0/ref/models/querysets/#prefetch-related and https://docs.djangoproject.com/en/3.0/ref/models/querysets/#select-related – neverwalkaloner Jan 23 '20 at 16:39
Added example with prefetch_related to make it clear. – neverwalkaloner Jan 23 '20 at 17:40

score 1 · Answer 2 · answered Jan 23 '20 at 17:54

Select related. Let me explain little bit. I added dummy data to explain.

class City(models.Model):
    name = models.CharField(max_length=35)
    state = models.ForeignKey('states.State', on_delete=models.CASCADE)

class Street(models.Model):
    name = models.CharField(max_length=35)
    building = models.ForeignKey('buildings.Building', on_delete=models.CASCADE)
    city = models.ForeignKey('cities.City', on_delete=models.CASCADE)

Your city table should be.

id    name         state
1   Cityname1      state1  
2   Cityname2        2

Your Street table should be.

id  name   city ..
1   st 1    1
2   stno.2  1
3   st no3  2

If your orm query will be this.

street = Street.objects.select_related('city')

This query combine two table as single.This means all city id foreign key will joining to each street id to create new table as follows.It will return three record because we are using select related city and main table in this case is Street so it will return 3 record. Main table in all case will be return first in dajngo.

 id   name   city ..  city.id  city.name  city.state
  1   st 1    1         1      Cityname1   state1
  2   stno.2  1         1      Cityname1   state1
  3   st no3  2         2      Cityname2    2

Django prefetch and select related

2 Answers2