CODEWITHSUNDEEP
djangodjango-models
John
John

Reputation: 4261

Django query for 3 tables

I have the following model structure Country, City(fk=country) and School(fk=city). Now I want to get list of countries with city, if city has a school. In my template, I want to do

for country in countrylist
    for city in getcitieswithschool

Can I get resultset with one query?

Upvotes: 1

Views: 93

Answers (2)

Alexey Savanovich
Alexey Savanovich

Reputation: 1903

You can get them using this query

from django.db.models import Count

countrylist = Country.objects.all()\
    .annotate(cities_cnt=Count('city'), schools_cnt=Count('city__school'))\
    .filter(cities_cnt__gt=0, schools_cnt__gt=0)

Upvotes: 1

DrTyrsa
DrTyrsa

Reputation: 31951

Get cities with

City.objects.filter(school__isnull=False).distinct().select_related('country')

Then {% regroup %} them by country and it will be clean and efficient.

Upvotes: 2

Related Questions