django annotate - issue with counting nested items in the template-CodePudding

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  id="business-challange-list">
    <strong >Business challanges</strong><br>
    {% for bc_category in business_challange %}
    <div >{{bc_category.title}}</div>
        {% for bc in bc_category.category_business_challange.all %}
        <div >
            <input  type="checkbox" value="{{bc.title}}" id="{{bc.title}}" data-filter="business_challange">
            <label  for="{{bc.title}}">
                {{bc.title}}
            </label>
            <span >{{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

CodePudding user response：

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.