CODEWITHSUNDEEP
pythondjangoormdjango-views
popcorn
popcorn

Reputation: 408

Django prefetch and select related

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?

Upvotes: 0

Views: 2864

Answers (2)

Gorkhali Khadka
Gorkhali Khadka

Reputation: 835

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

Upvotes: 1

neverwalkaloner
neverwalkaloner

Reputation: 47364

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)

Upvotes: 3

Related Questions