SQL Queries optimization for a model method

Using Django
1

Hello everyone,

I’m trying to optimize SQL queries.

I have a model Company that has a method to count related objects.

class Company(models.Model):
    name = models.CharField(max_length=100)
    address = models.ForeignKey(Address, on_delete=models.PROTECT, default=None, null=True, 
              blank=True)
    ...

@property
def count_published_certificate(self):
    return self.certificate_set.filter(published=True).count()

In the View the queryset is already ‘optimized’ for the related Address obj:

def company_list(request):
    company_result = Company.objects.all().select_related('address').order_by('name')
    form = CompanySearchForm()
    return render(request, 'address_book/company_list.html',
                  {'form': form, 'company_results': company_result}
                  )

Finally in the template I make a loop and call the ‘count_published_certificate’ method on each loop iteration which cause a SQL query every iteration → BAD !

<ul id="list_company">
    {% for company in company_results %}
        <li class="company">
            <div class="row">
                <div id="" class="col-md-4">
                <a href="{% url 'company-detail' company.id %}">
                    <span class="company_name">{{ company.name|upper }}</span>
                    <span>{% if company.legal_form %} {{ company.legal_form|upper }}{% endif %}</span>
                </a>
                </div>
                <div id="" class="col-md-2">
                    {{ company.address.city|upper }}
                </div>
                <div id="" class="col-md-2">
                    {{company.address.zip|upper}}
                </div>
                <div id="" class="col-md-2">
                    {{company.address.country|upper}}
                </div>
                <div id="" class="col-md-2">
                    {{company.count_published_certificate}}
                </div>
            </div>
        </li>
    {% endfor %}
</ul>

How can I optimize this ?

I did think of creating a new field in the Company model. Increase or decrease a counter each time a Certificate obj with the field published=True is saved. But it seems very ‘non elegant’ option…

Any help would be much appreciated…

2

There’s no need to define a new variable. Use the annotation facility to dynamically inject the count for each Company.

See the Cheat Sheet on the Aggregation page for examples of what you’re trying to do.

3

If someone needs the solution of my issue:
As Ken indicated I made a annotation (including a filter) to only count the published certificates

Inn my view I changed the queryset by this:

filters = Q(certificate__published=True)
company_result = Company.objects.all().select_related('address').order_by('name').\
    annotate(certificates=Count('certificate', filters))

Many thanks to Ken (Super Hero of Django Forum…-