How can I optimize queries django

Question

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

What is this city_dict being used for?

Iain Shelvington
– Iain Shelvington

2021-03-04 21:20:08 +00:00
Commented Mar 4, 2021 at 21:20 — Iain Shelvington
– Iain Shelvington, Commented Mar 4, 2021 at 21:20
I am using that as a response to ajax request.

Issa
– Issa

2021-03-04 21:22:29 +00:00
Commented Mar 4, 2021 at 21:22 — Issa
– Issa, Commented Mar 4, 2021 at 21:22

Iain Shelvington · Accepted Answer · 2021-03-04 21:30:50Z

1

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)

answered Mar 4, 2021 at 21:30

Iain Shelvington

32.5k3 gold badges36 silver badges55 bronze badges

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

Comments

willeM_ Van Onsem · Accepted Answer · 2021-03-04 21:23:44Z

1

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
    }

answered Mar 4, 2021 at 21:23

willeM_ Van Onsem

482k33 gold badges483 silver badges624 bronze badges

1 Comment

Issa Over a year ago

wow! this has reduced the queries from 30 to 9 queries. thanks for your help

Collectives™ on Stack Overflow

How can I optimize queries django

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest