Django query set for sums records each month

Question

I have the following models structure:

 Product  |  Price | Quantity |  Total*  | Date of purchase
Product A |   10   |    1     |    10    | 1/01/2020
Product B |   10   |    2     |    20    | 1/02/2020

*totale is created with a manager function in the models.py.

I want to have the sum for each month of each type of product in another app of my project. Something like this:

Product   | Gen | Feb | Mar | Apr | May | Jun | ....
Product A | 10  |  0  |  0  |  0  |  0  | 0   | ....
Product A |  0  | 20  |  0  |  0  |  0  | 0   | ....

This is my models.py

 class Product(models.Model):
        nome= models.CharField()

   class MaterialeManager(models.Manager):
    def get_queryset(self, *args, **kwargs):
        return super().get_queryset(*args, **kwargs).annotate(
            total=F('quantity')*F('price'),
        )

    def get_monthly_totals(self):
        products = dict((p.id, p) for p in Products.objects.all())
        return list(
            (product, datetime.date(year, month, 1), totale)
            for product_id, year, month, totale in (
                    self.values_list('product__nome', 'date__year', 'date__month')
                    .annotate(totale=Sum(F('quantity') * F('price')))
                    .values_list('product__nome', 'date__year', 'date__month', 'totale')
        )

    class Materiale(models.Model):
        product= models.ForeignKey(Product, on_delete=models.SET_NULL, null=True)
        quantity=models.DecimalField()
        price=models.DecimalField()
        date=models.DateField()
        obejcts=MaterialManager()

But I try the following code to figure out but doesn't work:

views.py

def conto_economico(request):
    elements = Materiale.objects.all()
    context= {
        'elements':elements,
            }
    return render(request, 'conto_economico/conto_economico.html', context)

template.html

{% for e in elements %}
              {{e.totale}}
{% endfor %}

Answer 1

Ok, I added year, but you can remove it if you don't want it.

Materiale.objects
    .values_list('product__nome', 'date__year', 'date__month')
    .annotate(totale=Sum(F('quantity') * F('price')))
    .values_list('product__nome', 'date__year', 'date__month', 'totale')

So the first .values_list triggers the group by, the annotate add the sum, and finally a values_list again to retrieve the result.

Example for usage;

import datetime
from somewhere import Products

def show_monthly_data(request):
    products = dict((p.id, p) for p in Products.objects.all())
    defaults = dict((datetime.date(2020, m, 1), 0) for m in range(1, 13))

    totals = {}
    for product_id, year, month, totale in (
        Materiale.objects
            .values_list('product__nome', 'date__year', 'date__month')
            .annotate(totale=Sum(F('quantity') * F('price')))
            .values_list('product__nome', 'date__year', 'date__month', 'totale')
    ):
        product = products[product_id]
        if product not in totals:
            totals[product] = dict(defaults)  # this makes a copy
        totals[product][datetime.date(year, month, 1)] = totale
    # You could add something to map the products with the totals
    return render(request, 'templates/template.html', {})  # etc


# Example to adding it to the Manager
class MaterialeManager(models.Manager):
    def get_queryset(self, *args, **kwargs):
        return super().get_queryset(*args, **kwargs).annotate(
            total=F('quantity')*F('price'),
        )

    def get_monthly_totals(self):
        products = dict((p.id, p) for p in Products.objects.all())
        return list(
            (products[product_id], datetime.date(year, month, 1), totale)
            for product_id, year, month, totale in (
                    self.values_list('product__nome', 'date__year', 'date__month')
                    .annotate(totale=Sum(F('quantity') * F('price')))
                    .values_list('product__nome', 'date__year', 'date__month', 'totale')
        )

Edit:

So you followed the method and added the method to model manager. Now this method is available in your view. So next step is use this method so you get your elements.

def conto_economico(request):
    context= {
        'elements': Materiale.objects.get_monthly_totals(),
    }
    return render(request, 'conto_economico/conto_economico.html', context)

So the method returns a list of tuples. But there are two problems with the data.

1. Data is not grouped per product
2. Data is has no zero values for months where nothings has been sold.

Problem 1 could be fixed with adding an order_by, but this doesn't solve the second problem. Thus what we need to do in the view is work the data so it is workable in the template.

So what is workable. We want one product with a list of 12 values, for each month one. So we can prepare a list, of zeroes and update the zeroes where we have data.

def conto_economico(request):
    defaults = list(0 for m in range(12))
    elements = dict()
    for product, date, totale in Materiale.objects.get_monthly_totals():
        # First check if product is already part of our elements.
        # If not, add the defaults for this product to your elements
        if product not in elements:
            elements[product] = list(defaults)
        # Second, find the index where to update the totale
        index = date.month - 1  # jan is one, but on index 0
        # Update the value
        elements[product][index] = totale

    context= {'elements': elements}
    return render(request, 'conto_economico/conto_economico.html', context)

So now we can focus on rendering the elements. We know elements is a dictionary, with as key a products and as value a list of totals.

<table>
   <tr>
       <td>Product</td>
       <td>Jan</td>
       <td>...</td>
   </tr>
{% for product, totals in elements.items %}
   <tr>
       <td>{{ product.name }}</td>
       {% for total in totals %}
       <td>{{ total }}</td>
       {% endfor %}
   <tr>
{% endfor %}
</table>