Edgar Navasardyan
Edgar Navasardyan

Reputation: 4511

Prefetch_related in Django views results in surplus database queries

I have blown my mind trying to figure what I am doing wrong in this code:

contacts = Contact.objects.user(request.user).prefetch_related(
                        Prefetch('contact_phone',
                                 queryset = ContactPhone.objects.order_by('value')))

    for contact in contacts:
        for contact_phone in contact.contact_phone.all():
            # do something

Django hits database several times. Any ideas ? Can it be that I can use prefetch_related in Django templates, and in views it hits database several times ???

For 10 contacts I have 10 database hits, each one looking like this:

SELECT ... FROM `contact_phone` WHERE `contact_phone`.`contact_id` = ...
ORDER BY `contact_phone`.`id` ASC LIMIT 1

So, there seems to be two unclear things to me. First, why does Django hit the database for each contact. And second, why does it limit phone number to 1 record ?

The respecive model looks like this:

class ContactPhone(BaseMixin):
    contact = models.ForeignKey(Contact, models.CASCADE, related_name = 'contact_phone')

Upvotes: 1

Views: 793

Answers (1)

dukebody
dukebody

Reputation: 7195

You get one query per Contact because in the prefetch_related you sort by value, while in the iteration you don't sort by any attribute. If the executed query doesn't match the prefetched one, a new query is done.

See the note at https://docs.djangoproject.com/en/2.0/ref/models/querysets/#prefetch-related:

Remember that, as always with QuerySets, any subsequent chained methods which imply a different database query will ignore previously cached results, and retrieve data using a fresh database query.

To avoid extra database queries do:

contacts = Contact.objects.user(request.user).prefetch_related(
                        Prefetch('contact_phone',
                                 queryset = ContactPhone.objects.order_by('value')))

    for contact in contacts:
        for contact_phone in contact.contact_phone.order_by('value'):
            # do something

Upvotes: 1

Related Questions