[Answer]-Complicated Django annotation with self reference

by

1👍

✅

A floor which is the top Floor is surely one which has no other Floor referring to it as 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" FROM "core_floor" WHERE NOT ("core_floor"."id" IN (SELECT U0."previous_floor_id" FROM "core_floor" U0 WHERE U0."previous_floor_id" IS NOT NULL))

HOWEVER MySQL is not great at nested queries so it may be more efficient to 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 to get rooms on top floors:

Room.objects.filter(floor__in=top_floors)
👤ACGray

Leave a comment