CODEWITHSUNDEEP
django
Issa
Issa

Reputation: 13

How can I optimize queries django

I have a function for getting a dictionary of country names as keys and their cities' names as values. Here is what i did which does not look to be the best way to do it. I am using debugging toolbar that shows 30 sql queries are made for getting this. How should i do it the right way?

#models.py
class City(models.Model):
    ci_id = models.AutoField(primary_key=True)
    ci_name = models.CharField(max_length=50, blank=True, null=True)
    country_co = models.ForeignKey('Country', on_delete=models.CASCADE, null=False)

   


class Country(models.Model):
    co_id = models.AutoField(primary_key=True)
    co_name = models.CharField(max_length=50, blank=True, null=True)

#views.py

def get_country_city():
    city_dict={}
    countries = Country.objects.all()
    for country in countries:
        c_list =[]
        cities = City.objects.filter(country_co__co_id=country.co_id)
        for city in cities:
            c_list.append(city.ci_name)
        city_dict[country.co_name] = c_list
    return city_dict

Upvotes: 1

Views: 49

Answers (2)

Iain Shelvington
Iain Shelvington

Reputation: 32294

You can do this with a single query

from collections import defaultdict
city_dict = defaultdict(list)
for co_name, ci_name in City.objects.values_list('country_co__co_name', 'ci_name'):
    city_dict[co_name].append(ci_name)

Upvotes: 1

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477598

You can work with .prefetch_related(…) [Django-doc] to fetch all the Citys with one extra query:

def get_country_city():
    countries = Country.objects.prefetch_related('city_set')
    return {
        country.co_name: [city.ci_name for ci in country.city_set.all()]
        for country in counties
    }

You can further optimize this by only fetching the relevant columns with .only(…) [Django-doc] and a Prefetch object [Django-doc] and minimize bandwith, although that will usually not make it run dramatically faster:

from django.db.models import Prefetch

def get_country_city():
    countries = Country.objects.prefetch_related(
        Prefetch(
            'city_set',
            City.objects.only('country_co', 'ci_name'),
            to_attr='cities'
        )
    ).only('co_id', 'co_name')
    return {
        country.co_name: [city.ci_name for ci in country.cities]
        for country in counties
    }

Upvotes: 1

Related Questions