django annotate - issue with counting nested items in the template

Question

I have a list of checkboxes and labels for business challanges. I'd like to apply categories for each business challange but I am struggling to count number of business challange in each category.

This is what I have:

models.py

class BusinessChallangeCategory(models.Model):
    title = models.CharField(max_length=300)
        
class BusinessChallange(models.Model):
    title = models.CharField(max_length=300)
    category = models.ForeignKey(BusinessChallangeCategory, on_delete=models.CASCADE, related_name='category_business_challange')

class Product(models.Model):
    title = models.CharField(max_length=300)
    business_challange = models.ManyToManyField(BusinessChallange, related_name='business_challange_product')

views.py

class ManualSearch(ListView):
    template_name = 'test.html'
    model = Product
    queryset = Product.objects.filter(status=1)
    
    def get_context_data(self, **kwargs):
        context = super(ManualSearch, self).get_context_data(**kwargs)
        business_challange = BusinessChallangeCategory.objects.annotate(business_challange_count=Count('category_business_challange__business_challange_product'))

        context['business_challange'] = business_challange
        return context

test.html

<div class="filter-container" id="business-challange-list">
    <strong class="f-12">Business challanges</strong><br>
    {% for bc_category in business_challange %}
    <div class="fw-bold f-12">{{bc_category.title}}</div>
        {% for bc in bc_category.category_business_challange.all %}
        <div class="form-check d-flex business-challange-item">
            <input class="form-check-input" type="checkbox" value="{{bc.title}}" id="{{bc.title}}" data-filter="business_challange">
            <label class="form-check-label ps-2" for="{{bc.title}}">
                {{bc.title}}
            </label>
            <span class="ms-auto ps-2 text-muted f-12">{{bc.business_challange_count}}</span> #count is not working, it doesn't appear at all
        </div>
        {% endfor %}
    {% endfor %}
</div>

Any advice on what I am doing wrong and how to calculate properly each business challange in each category will be appreciated

score 1 · Accepted Answer · answered Aug 22 '22 at 12:08

You are annotating the value in one queryset in template business_challange but then trying to access it while iterating another queryset bc_category.category_business_challange.all, which calls DB each time it runs. You don't need the annotation though you can call directly {{bc.business_challange_product.count}}. This will each issue another query though.

From performance perspective I would recommend annotating the count on the second queryset of BusinessChallange directly in view, instead of the BusinessChallangeCategory and group by category in python, so you have what you neeed in one query and one forloop iteration.

django annotate - issue with counting nested items in the template

1 Answers1