Effeciently Lookup Multiple Model Foreign Key Sets in Django

I have two models:

from django.db import Models
LANGUAGES = (
  ('en','English'),
  ('es','Spanish'),
)
class Group(models.Model):
  key = models.CharField(max_length=200)

class Data(models.Model):
  group = models.ForeignKey('Group')
  lang = models.CharField(max_length=2,choices=LANGUAGES)
  ...

I'm planning on generating an xml file:

<?xml version="1.0"?>
<groups>
  <group key="key1">
    <data lang="en">Some Data</data>
    <data lang="es">Some Data</data>
  </group>
  ...
</groups>

The xml file will contain all Group and Data entries in the table. I want to avoid looping through every Group, g, and calling g.data_set.all(), which is O(n) database connections with respect to rows in the Group table.

My first thought was to create a dictionary: {'key':[data1,data2,...],...} by

data = Data.objects.select_related('group').all()
groups = {}
for d in data.iterator():
  key = d.group.key
  if key in groups:
    groups[key].append(d)
  else:
    groups[key] = [d]

This will work decently for this case, but if I wanted to add another field to Group, things would start to get complicated. For lookups on more complicated data sets, this sort of approach may not work. Ideally, I could make Dja开发者_开发技巧ngo lookup Group.data_set in a query similar to, Group.objects.all(), then access it normally - group.data_set.all() - without causing another database hit.

If you want to minimize the database usage as much as possible you can pull everything out and then use python to create a dict of results. Big disadvantage of this might be memory usage.

data = list(Data.objects.all().values()) # create a list of all list
groups = [(x.key, x.id) for x in Group.objects.all()] # get a list of group key & id's
results = {}
for key, id in groups:
    results[key] = [x for x in data if x['group_id'] == id]

In Django 1.3 you can get really nice logging information right in the interactive shell:

>>> import logging
>>> l = logging.getLogger('django.db.backends')
>>> l.setLevel(logging.DEBUG)
>>> l.addHandler(logging.StreamHandler())

So now when you try different queries:

>>> groups = Group.objects.all() 

>>> list(groups) # Querysets are lazy!
(0.000) SELECT "groups_group"."id", "groups_group"."key" FROM "groups_group"; args=()
<<< 
[<Group: Group object>,
 <Group: Group object>]

Pretty useful!