SQL Queries optimization for a model method

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, 

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>
                <div id="" class="col-md-2">
                    {{ company.address.city|upper }}
                <div id="" class="col-md-2">
                <div id="" class="col-md-2">
                <div id="" class="col-md-2">
    {% endfor %}

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…

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.

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…-