# SQL Queries optimization for a model method

**URL:** <https://forum.djangoproject.com/t/sql-queries-optimization-for-a-model-method/7696>\
**Category:** Using Django\
**Created:** [April 27, 2021, 2:51pm UTC](https://forum.djangoproject.com/t/sql-queries-optimization-for-a-model-method/7696 "2021-04-27T14:51:57Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![axeljacobs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/axeljacobs/32/3939_2.png) [@axeljacobs](https://forum.djangoproject.com/u/axeljacobs)\
**Post date:** [April 27, 2021, 2:51pm UTC](https://forum.djangoproject.com/t/sql-queries-optimization-for-a-model-method/7696/1 "2021-04-27T14:51:57Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [April 27, 2021, 3:53pm UTC](https://forum.djangoproject.com/t/sql-queries-optimization-for-a-model-method/7696/2 "2021-04-27T15:53:10Z")

</div>

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](https://docs.djangoproject.com/en/3.2/topics/db/aggregation/#cheat-sheet) on the [Aggregation](https://docs.djangoproject.com/en/3.2/topics/db/aggregation/) page for examples of what you’re trying to do.

---

<div class="post-metadata">

**Author:** ![axeljacobs](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/axeljacobs/32/3939_2.png) [@axeljacobs](https://forum.djangoproject.com/u/axeljacobs)\
**Post date:** [April 27, 2021, 5:40pm UTC](https://forum.djangoproject.com/t/sql-queries-optimization-for-a-model-method/7696/3 "2021-04-27T17:40:46Z")

</div>

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