6

I'm trying to get a simple sum for a column with several rows in a queryset. My direct question is (a) how do I set get_queryset() to include a sum of a column and (b) how do I access that element in a template? Following this question: 

#models.py
class ItemPrice( models.Model ):
    price = models.DecimalField ( max_digits = 8, decimal_places=2 )
    ....

There are two answers provided - one using the .aggregate() method which I don't believe returns a queryset and .annotate() method which I believe appends an item to the queryset.

So, I would have expected that the following would add another item to the object list in this view:

#views.py
def get_queryset(self):
    # generate table and filter down to a subquery.
    queryset = ItemPrice.objects.filter(<some_filter>)
    # sum the price for each row in the subquery.
    queryset = queryset.annotate(totals=Sum('price'))
    return queryset

Then in the the Template, I would be able to iterate through the object list like this:

#template.html
{% for item in object_list %}
    {{ item }}
{% endfor %}

With the expectation that one of the items (the last item?) would be price_sum and that the balance could be accessed as price_sum.price.

However, when i add the following to my template, I get the prices for each line item - no summation. 

{% for item in object_list %}
    {{ item.totals }}
{% endfor %}

But, I can't access the item. I don't know if the problem is the view modification of the get_queryset() or if it's in the template?

Bill Armstrong
  • 1,615
  • 3
  • 23
  • 47
  • first of all you can combine both query no need to wirte in two lines.`queryset = ItemPrice.objects.filter().annotate(totals=Sum('price'))` and if you want the total of item price to be displayed then you can use template tag for that. – Abi Waqas May 09 '18 at 06:00
  • Thanks @AbiWaqas, I was trying to follow the original question. My original code was written exactly as you've suggested - but it still didn't produce the `annotate(Sum)` result that I was expecting. – Bill Armstrong May 09 '18 at 14:52

2 Answers2

6

if you would use: 

ItemPrice.objects.filter(<some_filter>).annotate(totals=Sum('price'))

totals always will be the same as 'price'

annotate (about Sum) uses like this:

if you have these models:

class ItemPrice( models.Model ):
    price = models.DecimalField ( max_digits = 8, decimal_places=2 )
    other_model = models.ForeignKey(
          to=OtherModel, 
          related_name="item_prices", 
          on_delete=models.SET_NULL
    )

# related_name - if I set related_name I can use like this
# other_model_object.item_prices.all() - this code return all 
# ItemPrices with other_model_id=other_model_object.id

class OtherModel(models.Model):
    some_field = models.CharField(max_lenght=256)

and you want to all price of all ItemPrices which have foreign key to one OtherModel you should use these code:

queryset = OtherModel.objects.annotate(
       total_prices=Sum('item_prices__price')
).filter(<your_filters>)

after that you can use:

for obj in queryset:
    print(obj.total_prices)

Or if you need sum of all prices you should use aggregate

ItemPrices.objects.aggregate(all_sum=Sum('price'))

this code return dict(or something like that, I do not remember exactly) like this 

{'all_sum': 1250}

all_sum - sum of all objects in your table in database

Andrei Berenda
  • 1,946
  • 2
  • 13
  • 27
  • I guess I didn't realize that you had to reference an `.annotate(Sum)` through a second model with a key. Is there not a simple way to have `view` side code that modifies the `queryset` to pass this information through? This doesn't seem very elegant. I know I can also use client side jQuery type code - but would like to keep it as a simple queryset return. – Bill Armstrong May 09 '18 at 14:49
  • I have understood what you need. You should not use annotate, because annotate add value to each object, but you need only one value(not to each object) and if you add this value to each object it won't be good. I think the best way is get two query the fiest is : queryset = ItemPrice.objects.filter() and the second: totals = queryset.aggregage(sum=Sum('price')).get('sum') and after that add these data to your template – Andrei Berenda May 09 '18 at 16:48
  • or you can sum in python code: totals = sum((item.price for item in queryset)) – Andrei Berenda May 09 '18 at 16:51
  • I think this is what I'm looking for - but I've now tried several ways to combine the `queryset` and `totals` to get a single "queryset" to return to the `get_queryset()` method. What am I missing - it seems that trying to concatenate (or combine) them doens't work. – Bill Armstrong May 10 '18 at 02:29
  • Or to ask it a different way - if i use the method described in your comment, how do I access that in the template? – Bill Armstrong May 10 '18 at 02:36
  • 2
    you can not combine it, because queryset returns same objects, but you need to return many similar objects(structure) and one with only one field(totals) – Andrei Berenda May 10 '18 at 03:18
  • Thanks - this is not as obvious - I now understand that a `get_queryset` is actually a database function and is basically a single object that can't be changed outside of what would generally be allowed through SQL (more or less). Although this isn't what I was looking for it was very helpful. – Bill Armstrong May 10 '18 at 03:50
5

if you want to add data to the template

queryset = ItemPrice.objects.filter(<your_filter>)
totals = queryset.aggregate(sum=Sum('price').get('sum')

context  = {
    'object_list': queryset,
    'totals': totals,
}
render(request, '<name_of_your_template>.html', context)

and in your template

{% for item in object_list %}
    # price of item
    {{ item.price }}
{% endfor %}
# total price
{{ totals }}
Jan Vorcak
  • 19,261
  • 14
  • 54
  • 90
Andrei Berenda
  • 1,946
  • 2
  • 13
  • 27
  • This is exactly what I was trying to accomplish through the `get_queryset()` method - when in fact it needs to be a `get_context_data()` method. It worked perfectly. – Bill Armstrong May 10 '18 at 03:52