Query optimization: howto

Question

I have my models like this:

class Personne(BaseModel):
    # [skip] many fields then:
    photos = models.ManyToManyField(Photo, blank=True,
                                    through='PersonnePhoto',
                                    symmetrical=False,
                                    related_name='parent')
    def photo_profil(self):
        a = PersonnePhoto.objects.filter(
            personne=self, photo_type=PersonnePhoto.PHOTO_PROFIL)
        return a[0] if len(a) else None

    # [skip] many fields then:
    travels = models.ManyToManyField(
            TagWithValue, blank=True,
            through='PersonneTravel',
            default=None, symmetrical=False,
            related_name='personne_travel')

class PersonneRelation(BaseModel):
    src = models.ForeignKey('Personne', related_name='src')
    dst = models.ForeignKey('Personne', related_name='dst')
    opposite = models.ForeignKey('PersonneRelation',
                                 null=True, blank=True, default=None)
    is_reverse = models.BooleanField(default=False)

I need to show all contacts of one person, and for each contact, show all his/her travels, and his/her photo.

Here's my code in my view:

class IndexView(LoginRequiredMixin, generic.TemplateView):
    template_name = 'my_home/contacts/index.html'

    def get_context_data(self, **kwargs):
        context = super(IndexView, self).get_context_data(**kwargs)
        context['contacts'] = Personne.objects.get(
                user=self.request.user).relations.all()
        return context

Pretty simple.

The problem is in my template. my_home/contacts/index.html includes contact_detail.html for each contact:

    {% for c in contacts %}
        {% with c as contact %}
            {% include 'includes/contact_detail.html' %}
        {% endwith %}
    {% endfor %}

In contact_detail.html, I call photo_profil, which makes a query to get the value of the picture of the contact.

{{ contact.photo_profil }}

This implies that if a user has 100 contacts, I will do one query for all his contacts, then 100 queries for each contact. How is it possible to optimize this? I have the same problem for travels, and the same problem for each ManyToMany fields of the contact, actually.

It looks like you can use [select_related](https://docs.djangoproject.com/en/1.9/ref/models/querysets/#select-related). You can also take a look at [this question](http://stackoverflow.com/questions/31237042/whats-the-difference-between-select-related-and-prefetch-related-in-django-orm). — Dirtycoder, Jan 18 '16 at 16:55
May I ask you to give just a sample that could work with, for example, "photo"? — Olivier Pons, Jan 18 '16 at 18:18
When I try to do something like `Personne.objects.get(user=self.request.user).relations.select_related('travels')` there's an error and Django wants only to select ForeignKey fields, not ManyToMany — Olivier Pons, Jan 18 '16 at 18:29

Alex Morozov · Accepted Answer · 2016-01-18T19:33:15.800

Looks like you need some prefetch_related goodness:

context['contacts'] = (Personne.objects
                       .get(user=self.request.user)
                       .relations
                       .all()
                       .prefetch_related('travels', 'photos'))

Note, however, that it will fetch all the contacts' photos, which is not you seem to expect. Basically you have two options here. Either add some hairy raw SQL to the Prefetch object's queryset parameter. Or (as I did in one of the projects) designate a separate main_photo field for storing the user's avatar (a separate ForeignKey to the Photo, I mean, not the completely independent one). Yes, it's clearly a denormalization, but queries become much more simple. And your users get a way to set a main photo, after all.

Query optimization: howto

1 Answers1