django - Modeling hierarchical location data -

django - Modeling hierarchical location data -

i've struggled problem time now, researched dozens of places, tried multiple methods i'm still struggling elegant , efficient solution. i'm model mastermind can me on track.

i'm trying model location info in using django 1.3. example, states have cities, cities have buildings, buildings, have rooms , rooms have devices/hardware. have working solution i'm running (n+1) number of query problem traversing relationships.

in views, want specify state , increment through of cities in state, building, rooms , devices. number of queries increment number of buildings, rooms, devices. currently, if want see of buildings, rooms, devices in state, perform query cities in state , utilize _set method follow foriegn keys backwards, looping city, looping buildings, looping rooms etc.

models.py

class city(models.model): city = models.charfield(max_length=128) city_code = models.charfield(max_length=6) state = usstatefield(_('state')) class address(models.model): address1 = models.charfield(max_length=128, blank=true) address2 = models.charfield(max_length=128, blank=true) city = models.foreignkey(city) zipcode= models.charfield(max_length=5, blank=true) zip_plus4=models.charfield(max_length=4, blank=true) class building(address, commonmodel): building = models.charfield(max_length=128, help_text='building name') building_code = models.charfield(max_length=2 ) class roomcode(models.model): roomcode = models.charfield(max_length=2, \ roomcode_name = models.charfield(max_length=64, help_text='description or name code') class room(models.model): building = models.foreignkey(building) name = models.charfield(max_length=128) roomcode = models.foreignkey(roomcode) class device(models.model): name = models.charfield(max_length=64, unique=true) description = models.charfield(max_length=64, blank=true) room = models.foreignkey(room)

currently city, building, room, device of state following,

views.py

@render_to('place/index.html') def index(request): sites = city.objects.distinct().order_by('state') homecoming { 'sites': sites, }

index.html, like...

{% site in sites %} {% building in site.building_set.all %} print building details {% room in building.room_set.all %} print room details {% device in room.device_set.all %} print device details

lots of loops , lots of queries!!

going other direction simple, if want devices , have location info can query using select_related

devices = device.objects.select_related().all()

questions:

what can improve model data? how can effeciently traverse relationships having (n+1) number of queries? mptt - trees? generic relationships? raw sql? something else? update

the main problem i'm trying solve linear increment of sql queries vs number of items in database. instance, have little amount of devices in databse, 32 devices, approx 20 cities, 30 buildings, , 35 rooms. querying devices using select_related uses 4 queries , takes 300 ms sql time, since can follow foreign keys.

going other direction, displaying states, cities, buildings, rooms , devices, ordered state requires 76 queries , takes 3231 ms sql time. adding 1 room, adds 1 query, couple devices in dozens of rooms bring page stand still.

select_related follows foreign keys , works great if need know city/building/room device in.

device = device.objects.get(id=1) room = device.room # hits database buidling = device.room.building # hits database again. device = device.objects.select_related().get(id=1) room = device.room # doesn't nail building = device.building # doesn't nail

the problem occurs other direction, stated in index.html illustration above, after retrieving city, have loop through each respective, building, room , device see devices, hitting database every city, building, room, device. benchmarking shows number of sql queries getting out of hand , increment page load time.

since objects hierarchical, mptt , generic relationships doesn't create sense. wouldn't set building room, example. of these relationships permanent, not, i.e. room never leaves building, device may. don't see problem current modelling scheme.

since you're concerned number of queries, take @ select_related feature of querysets. lets deep foreign relationships in single sql call, , cache them duration of request cycle. may alleviate weight of database transactions.

but, simple question you: have benchmarked number of queries? deadly performance of application?

on other hand, you've impressed lot of individuality collection describes little more than: [name, description, parent]. if re-wrote classes reflect structure, subclass of mptt handle relationships correctly (never letting building in room, example), , model entire thing in single tree. design generic "details , attributes" model contain items needed cities , addresses , like.

django django-models