0

I have Country, State and City models which are chained by foreign keys as shown below:

class Country(models.Model):
    name = models.CharField(max_length=20)

class State(models.Model):
    country = models.ForeignKey(Country, on_delete=models.CASCADE)
    name = models.CharField(max_length=20)
    
class City(models.Model):
    state = models.ForeignKey(State, on_delete=models.CASCADE)
    name = models.CharField(max_length=20)

Then, I iterate Country and State models with prefetch_related() as shown below:

for country_obj in Country.objects.prefetch_related("state_set").all():
    for state_obj in country_obj.state_set.all():
        print(country_obj, state_obj)

Then, 2 SELECT queries are run as shown below. *I use PostgreSQL and these below are the query logs of PostgreSQL and you can see my answer explaining how to enable and disable the query logs on PostgreSQL:

enter image description here

Next, I iterate Country, State and City models with prefetch_related() as shown below:

for country_obj in Country.objects.prefetch_related("state_set__city_set").all():
    for state_obj in country_obj.state_set.all():
        for city_obj in state_obj.city_set.all():
            print(country_obj, state_obj, city_obj)

Then, 3 SELECT queries are run as shown below:

enter image description here

Now, how can I reduce 3 SELECT queries to 2 SELECT queries or less for the example just above iterating Country, State and City models with prefetch_related()?

Super Kai - Kazuya Ito
  • 22,221
  • 10
  • 124
  • 129

1 Answers1

0

You can achieve that using nested prefetch_related.

prefetch_city_qs = City.objects.all()
prefetched_city = Prefetch('state_set', to_attr='prefetched_city_set',
                            query_set=prefetch_city_qs)

join_tables_2 = [prefetched_city]
prefetch_state_qs = State.objects.prefetch_related(*join_tables_2).all()
prefetched_state = Prefetch('state_set', to_attr='prefetched_state_set',
                            query_set=prefetch_state_qs)

join_tables_1 = [prefetched_state]

countries = Country.objects.prefetch_related(*join_tables).all()

then when you iterate, you should be access the same using loops. like

for country in countries:
   for state in country.prefetched_state_set:
       for city in state.prefetched_city_set:
           print(city, state, country)

Apologies for language and format as its my first response in stackoverflow

Jerrine Zachariah
  • 1