Accueil / Blog / Métier / Archives / An equivalent of Django's select_related for ManyToMany and OneToMany relationships

An equivalent of Django's select_related for ManyToMany and OneToMany relationships

Par Mathieu Leplatre publié 05/12/2011, édité le 11/03/2015

Using an ORM simplifies and reduces greatly the amount of code to interact with databases. Nevertheless, it can easily hide database design defects or become a source of serious performance issues.

A Common Pitfall

With Django, the most classic problem occurs while accessing objects relations attributes inside a loop. That's why QuerySet's method select_related() exists : it will join specified relations so that access to their attributes does not hit the database. Refer to Django's documentation for more information !

One-To-Many and Many-To-Many Relationships

select_related() is not able to follow One-To-Many (1-n) and Many-To-Many (n-n) relationships. The Django team is currently working on prefetch_related(). But before we can enjoy this future feature, we can implement an equivalent in python.

With these models :

class Pizza(models.Model):
    name = models.CharField(max_length=50)

class Restaurant(models.Model):
    name = models.CharField(max_length=50)
    pizzas = models.ManyToManyField(Pizza, through='PizzaRestaurant')

class PizzaRestaurant(models.Model):
    pizza = models.ForeignKey(Pizza)
    restaurant = models.ForeignKey(Restaurant)
    price = models.FloatField()

This loop will generate 1 + N queries :

for restaurant in Restaurant.objects.all():
    for pizza in restaurant.pizzas.all():
        print pizza.name

Whereas this one will only generate 2 queries :

# Store relationships in a dict
byrestaurant = {}
for pr in PizzaRestaurant.objects.select_related('restaurant', 'pizza').all():
    byrestaurant.setdefault(pr.restaurant.id, []).append(pr.pizza)
# Use stored lists
for restaurant in Restaurant.objects.all():
    for pizza in byrestaurant[restaurant.id]:
        print pizza.name

According to the amount of N, doing that trick in views can boost your pages !

This is not perfect and elegant, but if it allows you to downsize the number of queries from several thousands to fifteen, like it did on Memopol2, you can think twice.

ABONNEZ-VOUS À LA NEWSLETTER !
Voir aussi
Formation Django initiation à Toulouse du 13 au 15 mars Formation Django initiation à Toulouse du 13 au 15 mars 26/01/2017

Entrez de plain-pied dans l'univers de Django aux côtés de développeurs ayant une expérience de ...

Retour sur la PyConFr 2016 Retour sur la PyConFr 2016 18/10/2016

Nous étions présents à Rennes pour PyConFr 2016. Voici notre compte-rendu à chaud.

Wagtail: How to use the Page model and its manager (part 2) Wagtail: How to use the Page model and its manager (part 2) 08/08/2016

The Page model has several methods specific to Wagtail. This is also the case of its manager. We ...

Wagtail : How to make your own content type models (part 1) Wagtail : How to make your own content type models (part 1) 29/07/2016

We are used to initialize our CMS directly from a web interface, often including lots of complex ...

Presentation of the latest Django CMS: Wagtail Presentation of the latest Django CMS: Wagtail 22/07/2016

Wagtail is a quite recent Django CMS. However, its young age does not keep it from having a lot of ...