CookBook - preload_related()

Django's ManyToMany fields aren't sufficient when you need to provide additional data for the relation. Querying related objects can easily lead to dozens of SQL queries for just simple operations, and Django's select_related(True) doesn't help in this case.

preload_related() is a utility function which retrieves related objects for a list of objects already in memory. Here is a simplified example:

>>> users = User.objects.filter(username__startswith='a')
>>> preload_related(users,
...                 User,
...                 (UserProperty.objects, Property.objects, 'properties'),
...                 (UserAlias.objects   , Alias.objects   , 'aliases'   ))
>>> users[0].properties
[<a list containing Property objects>]
>>> users[0].aliases
[<a list containing Alias objects>]

UserProperty and UserAlias are the models creating many-to-many relations between the User model and the Property and Alias models. Here I'm asking that all Properties and Aliases which relate to a user in my users list are retrieved from the database and connected to the corresponding User objects. A list of Properties will be stored in each User object's properties attribute, and Aliases end up as lists in aliases attributes.

In addition, attributes properties_ids and aliases_ids will contain lists of primary keys for the related objects.

preload_related() will create one SQL query for each related model. This is a huge improvement, since previously every access to, say, user.userproperty_set.all() would run a separate SQL query. In one of my more complex views, this reduced the number of queries from over 6000 to just eight.

The attached file contains an implementation of preload_related() for the magic-removal branch.

-- akaihola 2006-04-05