python - Complicated Django annotation with self reference -

given 2 models:

• floor has reference called previousfloor can null first floor
• room of course can in 1 floor (but there multiple rooms in floor)

i need query returns rooms on top floor.

---

in order achieve that, created function in floor model called get_next_floor return floor previousfloor set self , return none if filter empty (this last floor). function in floor model called is_last_floor, return true if get_next_floor returned none, , otherwise returned false.

in end loop through rooms , have call is_last_floor floor room in, in order decide if fits condition.

this problem can optimized saving found last floor during loop.

since abstraction of real problem , working on large database, solution no longer viable option because of bad performance of loop.

is there way implement in 1 query annotation?

a floor top floor surely 1 has no other floor referring previousfloor.

so:

top_floors = floor.objects.exclude(id__in=floor.objects.filter(previous_floor__isnull=false).values_list('previous_floor', flat=true)) 

which translates to:

select "core_floor"."id", "core_floor"."name", "core_floor"."previous_floor_id" "core_floor" not ("core_floor"."id" in (select u0."previous_floor_id" "core_floor" u0 u0."previous_floor_id" not null)) 

however mysql not great @ nested queries may more efficient do:

lower_floors = floor.objects.filter(previous_floor__isnull=false).values_list('previous_floor', flat=true) top_floors = floor.objects.exclude(id__in=list(covered_floors)) 

see https://docs.djangoproject.com/en/dev/ref/models/querysets/#in

then rooms on top floors:

room.objects.filter(floor__in=top_floors)