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? — Ashraful
– Ashraful, Commented Jan 23, 2020 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
– popcorn, Commented Jan 23, 2020 at 16:12
@AshrafulIslam But how do I apply it for the .filter(). I would really appreciate a simple example. — popcorn
– popcorn, Commented Jan 23, 2020 at 16:16
For example streets = Street.objects.all().select_related('building') — Charnel
– Charnel, Commented Jan 23, 2020 at 16:20

neverwalkaloner · Accepted Answer · 2020-01-23 17:40:03Z

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, 2020 at 17:40

answered Jan 23, 2020 at 16:23

neverwalkaloner

47.6k7 gold badges101 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

popcorn Over a year ago

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

neverwalkaloner Over a year ago

@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 Over a year ago

Probably this links could be helpful docs.djangoproject.com/en/3.0/ref/models/querysets/… and docs.djangoproject.com/en/3.0/ref/models/querysets/…

neverwalkaloner Over a year ago

Added example with prefetch_related to make it clear.

Gorkhali Khadka · Accepted Answer · 2020-01-23 17:54:26Z

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

Collectives™ on Stack Overflow

Django prefetch and select related

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related