Django ORM issue for multiple tables

Question

This is school project university seat managaement, we have tables for city, then have localaity each city can have multiple locality, locality will have mutliple schools and scholls will have multiple seats available. We want show only citis that are available seats, whien click on city only show the locality which having seats and then schools that is only having seats available.

Here is model created.

class City(models.Model):
    city_name = models.CharField(max_length=100)


class Locality(models.Model):
    locality_name = models.CharField(max_length=100)
    city = models.ForeignKey(City, on_delete=models.CASCADE)


class School(models.Model):
    school_name = models.CharField(max_length=100)
    locality = models.ForeignKey(Locality, on_delete=models.CASCADE)


class Seat(models.Model):
    school = models.ForeignKey(School, on_delete=models.CASCADE)
    is_available = models.BooleanField(default=True)
                

Here is data from tables

seat_management_city

id	city_name
1	City A
2	City B

seat_management_locality

id	locality_name	city_id
1	Locality X	1
2	Locality Y	1
3	Locality Z	2

seat_management_school

id	school_name	locality_id
1	School 1	1
2	School 2	1
3	School 3	2
4	School 4	3

seat_management_seat

id	is_available	school_id
1	0	1
2	0	1
3	0	2
4	0	3
5	1	4

From from here it is clear that we want to only show City B on top because it only has a seat available.

But running ORM is not showing correct data.

If we try to get data using ORM, here is data we get it shows both the city record, instead of showing distinct one record of City B, it is showing both.

 >>> from seat_management.models import City, Locality, School, Seat
 >>>
 >>> cities = City.objects.filter(locality__school__seat__is_available=True).distinct()
 >>> for city in cities:
 ...     print(city.city_name)
 ...
 City A
 City B
 >>> sql_statement = str(cities.query)
 >>> print(sql_statement)
 SELECT DISTINCT "seat_management_city"."id", "seat_management_city"."city_name" FROM "seat_management_city" INNER JOIN "seat_management_locality" ON ("seat_management_city"."id" = "seat_management_locality"."city_id") INNER JOIN "seat_management_school" ON ("seat_management_locality"."id" = "seat_management_school"."locality_id") INNER JOIN "seat_management_seat" ON ("seat_management_school"."id" = "seat_management_seat"."school_id") WHERE "seat_management_seat"."is_available"
 >>> record_count = cities.count()
 >>> print(record_count)
 2
 >>> print(record_count)
 2

Same is happening with raw SQL in Django ORM, If we run raw sql in shell it is also showing wrong data.

>>> from django.db import connection
>>> sql_query = 'SELECT DISTINCT "seat_management_city"."id", "seat_management_city"."city_name" FROM "seat_management_city" INNER JOIN "seat_management_locality" ON ("seat_management_city"."id" = "seat_management_locality"."city_id") INNER JOIN "seat_management_school" ON ("seat_management_locality"."id" = "seat_management_school"."locality_id") INNER JOIN "seat_management_seat" ON ("seat_management_school"."id" = "seat_management_seat"."school_id") WHERE "seat_management_seat"."is_available"'
>>> with connection.cursor() as cursor:
...     cursor.execute(sql_query)
...     results = cursor.fetchall()
...
<django.db.backends.sqlite3.base.SQLiteCursorWrapper object at 0x00000212C7D70290>
>>> for row in results:
...     print(row)
...
(1, 'City A')
(2, 'City B')
>>>
   

But if we run same sql query in SQLite browser it shows correct data.

SQLite Browser data

Why this is happining?

In Django ORM it should show correct distinct data.