0

Given the two models:

  • Floor that has a reference to itself called previousFloor which can be null for the first Floor
  • Room which of course can only be in one Floor (but there are multiple rooms in a Floor)

I need a query that returns all rooms on the top floor.

In order to achieve that, I created a function in the Floor model called get_next_floor which would return the Floor with previousFloor set to self and return None if the filter was empty (this is the last Floor). So another function in the Floor model called is_last_floor, would return True if get_next_floor returned None, and otherwise would returned False.

In the end I would loop through all the rooms and have to call is_last_floor for the Floor the Room is in, in order to decide if it fits my condition.

This problem can be slightly optimized by saving the already found last Floor during the loop.

Since this is only an abstraction of my real problem and I am working on a large database, this solution is no longer a viable option because of the bad performance of the loop.

Is there a way to implement this in one query with annotation?

Martin Flucka
  • 3,125
  • 5
  • 28
  • 44
  • 1
    In the end I would loop through all the rooms and have to call is_last_floor for every Floor the Room is in, in order to decide if it fits my condition. ... I believe this is a mistake, as you stated that each room is in only one floor. – DerShodan Mar 24 '14 at 14:18

1 Answers1

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
  • 666
  • 3
  • 10
  • I was too quick to accept your answer... the problem is that in my case the first floor is the one that has no previousFloor – Martin Flucka Mar 24 '14 at 14:50
  • In that case you can surely just use `Floor.objects.filter(previous_floor__isnull=True)`? – ACGray Mar 24 '14 at 15:01
  • but that would only return the first level floors not the top level floors – Martin Flucka Mar 24 '14 at 15:03
  • 1
    My original answer does get you the top level floors. It will get you all the floors that don't have any other floors above them (i.e. there are no other floors which refer to them as previous floor) – ACGray Mar 24 '14 at 15:05