i want to get the type of enquiry and display the count for each product in that type

In views.py

def current(request):
    act=enquiry.objects.filter(type='Activity').values('product_name').distinct().annotate(Count('product_name'))
    wal=enquiry.objects.filter(type='Walkin').values('product_name').distinct().annotate(Count('product_name'))
    tele=enquiry.objects.filter(type='Tele').values('product_name').distinct().annotate(Count('product_name'))
    digital=enquiry.objects.filter(type='Digital').values('product_name').distinct().annotate(Count('product_name'))

In models.py

class product(models.Model):
    product_category=models.CharField(null=True,max_length=5000)
    product_category_id=models.CharField(null=True,max_length=5000)
    branch=models.CharField(default='',max_length=100)
    products=models.CharField(null=True,max_length=5000)
    created_at = models.DateTimeField(auto_now_add=True)
    updated_at = models.DateTimeField(default=timezone.now)
   
    def __str__(self):
     return '{},{}'.format(self.products, self.product_category)

class enquiry(models.Model):
    comment=models.CharField(default='',max_length=100,null=False)
    branch=models.CharField(default='',max_length=100)
    created_at = models.DateField(auto_now_add=True)
    updated_at = models.DateTimeField(default=timezone.now)
    created_by=models.CharField(default='',max_length=100)
    status=models.CharField(default='',max_length=100)
    commentupdate=models.CharField(default='',max_length=100)
    product=models.ForeignKey(product,models.CASCADE,default='')
    product_name=models.CharField(default='',max_length=100)
    product_category=models.CharField(default='',max_length=100)
    type=(
        ('Walkin','Walkin'),
        ('Activity','Activity'),
        ('TeleEnq','TeleEnq'),
        ('Digital','Digital'),
        )
    type=models.CharField(choices=type, default='',max_length=100)
    status_type=(
        ('Retail','Retail'),
        ('Closed','Closed'),
        )
    status_update=models.CharField(choices=status_type, default='open',max_length=100)

In html

  {%for pr in act%} 
  <tr>
  <td>{{forloop.counter}}</td>
  <td>{{pr.product_name}}</td>               
  <td>{{pr.product_name__count}}</td>
  {%endfor%}

  {%for pr in wal%}
  <td>{{pr.product_name__count}}</td>
  {%endfor%} 

  {%for pr in tele%}
  <td>{{pr.product_name__count}}</td>
  {%endfor%} 

  {%for pr in digital%}
  <td>{{pr.product_name__count}}</td>
  {%endfor%} 
  </tr>

I want output like this:

CodePudding user response：

Use annotation and aggregation

I think, from perusing that documentation, you would use the queryset

qs = Product.objects.all.annotate(
       walkin_count=Count('enquiry', filter=Q( enquiry__type='Walkin' ))
    ).annotate(
       activity_count=Count('enquiry', filter=Q( enquiry__type='Activity' ))
    ).annotate ...

Annotation adds fields to the retrieved objects, so in your template

{% for prod in products %}
    <tr>
      <td>{{prod}}</td>
      <td>{{prod.walkin_count}} </td>
      <td>{{prod.activity_count}} </td>
      ...
    </tr>

The last row with the totals, I'm not sure if you can add them to the same queryset in any way, but it's easy to compute them from the queryset:

total_walkins = sum( [ prod.walkin_count for prod in qs ] )

and pass these in the context to your template.

BTW its a Django/Python convention that (model) classes start with a capital letter. It confuses the heck out of me trying to interpret product as a model rather than an instance. I've probably capitalized out of habit in my answer.

CodePudding user response：

Firstfully, I have few basic suggestions to your style:

1. Don't use type as field/variable, ever. You can name it _type or type_ in example. Using builtin function's names is a very bad practise.
2. Don't use same name more than once for a model. It might override itself and behave unexpectedly or give an error (type again). Not to mention that way is less readable.
3. Classes' names should be written in CamelCase (enquiry - bad, Enquiry - better).
4. If you have ForeignKey in Enquiry to Product, it's not recommended to have also fields in Product that have the same value.

I advice you to learn about Django's model-relations (link at the bottom). For now, I'll give you some hints that might be helpful.

models.py:

Enquiry(models.Model):
    ...
    product = product=models.ForeignKey(Product, related_name='enquires', on_delete=models.CASCADE, default='')
    ...

Then you can access related object via:

an_enquiry = Enquiry.objects.get(id=1)
an_enquiry.product   # accesses the related object
an_enquiry.product.product_category   # returns the 'product_category' field value from related object

Or easily reverse if needed:

a_product = Product.objects.get(id=1)
all_queryset = a_product.enquires.all()   # returns all related objects in a QuerySet
wal_queryset = a_product.enquires.filter(a_type='Walkin')   # returns filtered QuerySet
wal_queryset.count()   # returns sum of objects in a queryset

And in your template:

{% for product in wal_queryset %}
    {{ product.wal_queryset.count }}
{% endfor %}

Read about it at the Django docs, cause that gives a lot of opportunities. Understanding relations makes Django very powerful tool.