Django ORM: Selecting related set


Question

Say I have 2 models:

class Poll(models.Model):
    category = models.CharField(u"Category", max_length = 64)
    [...]

class Choice(models.Model):
    poll = models.ForeignKey(Poll)
    [...]

Given a Poll object, I can query its choices with:

poll.choice_set.all()

But, is there a utility function to query all choices from a set of Poll?

Actually, I'm looking for something like the following (which is not supported, and I don't seek how it could be):

polls = Poll.objects.filter(category = 'foo').select_related('choice_set')
for poll in polls:
    print poll.choice_set.all() # this shouldn't perform a SQL query at each iteration

I made an (ugly) function to help me achieve that:

def qbind(objects, target_name, model, field_name):
    objects = list(objects)
    objects_dict = dict([(object.id, object) for object in objects])
    for foreign in model.objects.filter(**{field_name + '__in': objects_dict.keys()}):
        id = getattr(foreign, field_name + '_id')
        if id in objects_dict:
            object = objects_dict[id]
            if hasattr(object, target_name):
                getattr(object, target_name).append(foreign)
            else:
                setattr(object, target_name, [foreign])
    return objects

which is used as follow:

polls = Poll.objects.filter(category = 'foo')
polls = qbind(polls, 'choices', Choice, 'poll')
# Now, each object in polls have a 'choices' member with the list of choices.
# This was achieved with 2 SQL queries only.

Is there something easier already provided by Django? Or at least, a snippet doing the same thing in a better way.

How do you handle this problem usually?

1
18
5/12/2009 2:50:25 PM

Accepted Answer

Update: Since Django 1.4, this feature is built in: see prefetch_related.

First answer: don't waste time writing something like qbind until you've already written a working application, profiled it, and demonstrated that N queries is actually a performance problem for your database and load scenarios.

But maybe you've done that. So second answer: qbind() does what you'll need to do, but it would be more idiomatic if packaged in a custom QuerySet subclass, with an accompanying Manager subclass that returns instances of the custom QuerySet. Ideally you could even make them generic and reusable for any reverse relation. Then you could do something like:

Poll.objects.filter(category='foo').fetch_reverse_relations('choices_set')

For an example of the Manager/QuerySet technique, see this snippet, which solves a similar problem but for the case of Generic Foreign Keys, not reverse relations. It wouldn't be too hard to combine the guts of your qbind() function with the structure shown there to make a really nice solution to your problem.

13
10/2/2014 5:46:52 PM

Time has passed and this functionality is now available in Django 1.4 with the introduction of the prefetch_related() QuerySet function. This function effectively does what is performed by the suggested qbind function. ie. Two queries are performed and the join occurs in Python land, but now this is handled by the ORM.

The original query request would now become:

polls = Poll.objects.filter(category = 'foo').prefetch_related('choice_set')

As is shown in the following code sample, the polls QuerySet can be used to obtain all Choice objects per Poll without requiring any further database hits:

for poll in polls:
    for choice in poll.choice_set:
        print choice

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon