Optimizing queries in Django, prefact_related for objects that are reversely linked
<p>In my django project I have 3 models, simplified for this example: Contact, WorkRelation and Group objects.</p>

<p><strong>Contact</strong></p>

<pre><code>class Contact(BaseModel):
    title = models.CharField(max_length=30, blank=True)
    initials = models.CharField(max_length=10, blank=True)
    first_name = models.CharField(max_length=30, blank=True)
    prefix = models.CharField(max_length=20, blank=True)
    surname = models.CharField(max_length=30)

    def get_workrelations(self):
        workrelations = apps.get_model('groups', 'WorkRelation')
        return workrelations.objects.filter(contact=self)

    def get_organisations(self):
        output = ""
        strings = []
        wr = self.get_workrelations()
        for relation in wr:
            group = relation.group
            name = group.name
            strings.append(s)
        if len(strings) &gt; 0:
            output = ", ".join(strings)
        return output
</code></pre>

<p><strong>WorkRelation</strong></p>

<pre><code>class WorkRelation(BaseModel):
    contact = models.ForeignKey(Contact, on_delete=models.CASCADE)
    group = models.ForeignKey(Group, on_delete=models.CASCADE)
    function = models.CharField(max_length=40, blank=True)
    email_address = models.EmailField()
    phone_number = models.CharField(max_length=13, blank=True)
    description = models.TextField(max_length=400, blank=True)
</code></pre>

<p><strong>Group</strong></p>

<pre><code>class Group(BaseModel):
    group_type = models.ForeignKey(GroupType, on_delete=models.CASCADE)
    name = models.CharField(max_length=60, unique=True)
    street_name = models.CharField(max_length=40, blank=True)
    house_number = models.CharField(max_length=10, blank=True)
</code></pre>

<p>The problem with this setup is that it becomes extremely slow when I want to call get_organisations() on a large contact set. For example: when trying to list all my contacts (600 for my demo set), and call get_organisations(), about 1250 queries are needed.</p>

<p>I found that you can prevent this by using prefetch_data(), but somehow I can't get this working in my setup. I tried to replace my query for </p>

<pre><code>queryset = Contact.objects.prefetch_related('workrelation_set')
</code></pre>

<p>But this didn't speed it up (against my presumption). Do you guys know if it is even possible to speed this up?</p>

<p>Change your get_organisations code to this:</p>

<pre><code>def get_organisations(self):
    return ', '.join(
        workrelation.group.name for workrelation
        in self.workrelation_set.all()
    )
</code></pre>

<p>And use this query for fetching Contact objects:</p>

<pre><code>Contact.objects.prefetch_related('workrelation_set__group')
</code></pre>

<p>This will return the result with a single query.</p>

python django django-queryset