M.Rob M.Rob - 5 months ago 5
Python Question

Django Sum of Multiplied Values

I am trying to show the total weight of assets I have presented on a table.

Each Asset has a weight and a quantity and I can present the total weight of each asset as below:

def by_item_weight(self):
"""This Method gives the total value of the asset. Weight * Qnty"""
total = self.asset_quantity * self.asset_weight
return total


What I would like to do is take the total item weight of each asset (result of qnty * weight) and present the over all weight of all assets.

I have tried to make various combinations of the below to return the sum of the by_item_weight 'column':

Asset.objects.all().annotate(total=Sum('by_item_weight')


But I understand this will not work as by-item-weight is not defined in the model Asset.

I am presenting the table via a for loop as below on my assetslist.html template:

{% block content %}



<table class="well table table-striped text-center">
<thead>
<tr class="text-center">
<th class="text-center">Asset ID:</th>
<th class="text-center">Asset Name:</th>
<th class="text-center">Asset Quantity:</th>
<th class="text-center">Asset Weight / kg:</th>
<th class="text-center">Total Weight / kg:</th>
<th class="text-center">Asset Owner:</th>
</tr>
</thead>
<tbody>
<tr class="text-center">
{% for asset in object_list %}
<td>{{ asset.id }}</td>
<td>{{ asset.asset_name }}</td>
<td>{{ asset.asset_quantity }}</td>
<td>{{ asset.asset_weight }}</td>
<td>{{ asset.by_item_weight }}</td>
<td>{{ asset.asset_owner }}</td>


</tr>


{% endfor %}


I am new to Python and am having trouble finding a way to capture the value of each asset.by_item_weight which If I could capture in a list I presume I could them sum the list and show the result.

My Models

class Asset(models.Model):
asset_name = models.CharField(max_length=30)
asset_quantity = models.IntegerField(default=0)
asset_weight = models.IntegerField(default=0)
asset_owner = models.ForeignKey(
'AssetOwner',
on_delete=models.CASCADE,
) # This should be a Foreign Key Drop down of AssetOwners owner_name.


def by_item_weight(self):
"""This Method gives the total value of the asset. Weight * Qnty"""
total = self.asset_quantity * self.asset_weight
return total

def __str__(self):
return '{}'.format(self.asset_name)
return 'Asset Quantity: {}'.format(self.asset_quantity)
return 'Asset Weight: {}'.format(self.asset_weight)
return 'Asset Owner: {}'.format(self.asset_owner)
return 'Asset Owner: {}'.format(self.asset_owner)


Any help would be much appreciated.

UPDATED:

No Errors now, but still not able to show/display the value of sum_total

New Template

{% extends "personal/header.html" %}

{% block content %}

<h1 class='text-center'>This is the full asset list not split by owner</h1></br>

<table class="well table table-striped text-center">
<thead>
<tr class="text-center">
<th class="text-center">Asset ID:</th>
<th class="text-center">Asset Name:</th>
<th class="text-center">Asset Quantity:</th>
<th class="text-center">Asset Weight / kg:</th>
<th class="text-center">Total Weight / kg:</th>
<th class="text-center">Asset Owner:</th>
</tr>
</thead>
<tbody>
<tr class="text-center">
{% for asset in object_list %}
<td>{{ asset.id }}</td>
<td>{{ asset.asset_name }}</td>
<td>{{ asset.asset_quantity }}</td>
<td>{{ asset.asset_weight }}</td>
<td>{{ asset.by_item_weight }}</td>
<td>{{ asset.asset_owner }}</td>


</tr>


{% endfor %}

</tbody>
</table>


<p class="" style="">Total Weight In Stock : {{ asset.sum_total }}</p>


<p class="text-center">{% include "sam/includes/backtosam.html" %}</p>



{% endblock %}


New Models

from __future__ import unicode_literals
from django.db import models
from django.db.models import Sum, F, Count
from django.db.models import Max
from django.db.models import ExpressionWrapper
from django.db.models import Aggregate

class Asset(models.Model):
asset_name = models.CharField(max_length=30)
asset_quantity = models.IntegerField(default=0)
asset_weight = models.IntegerField(default=0)
asset_owner = models.ForeignKey(
'AssetOwner',
on_delete=models.CASCADE,
) # This should be a Foreign Key Drop down of AssetOwners owner_name.
def by_item_weight(self):
"""This Method gives the total value of the asset. Weight * Qnty"""
total = self.asset_quantity * self.asset_weight
return total

def sum_total(self):
assets = Asset.objects.all().annotate(
total_weight=ExpressionWrapper(F('asset_quantity') * F('asset_weight'),output_field=IntegerField))
the_sum = assets.aggregate(total=Sum('total_weight'))
return the_sum

def __str__(self):
return '{}'.format(self.asset_name)
return 'Asset Quantity: {}'.format(self.asset_quantity)
return 'Asset Weight: {}'.format(self.asset_weight)
return 'Asset Owner: {}'.format(self.asset_owner)
return 'Asset Owner: {}'.format(self.asset_owner)
return 'Total Weight of Assets: {}'.format(self.assets)


Updated view

from django.shortcuts import render
from django.http import HttpResponse
from django.core.cache import cache
from django.db.models import Sum, F

def get_total_weight(self):

total_weight = cache.get('total_weight',-1)
if total_weight == -1:
total_weight = Asset.objects.annotate(total_weight=F('asset_quantity')*F('asset_weight')).aggregate(total=Sum('total_weight'))
# this is tested
cache.set('total_weight',total_weight)
return total_weight

def index(request):

return render(request, 'sam/index.html')

def assetslist(request):

return render(request,'sam/assetslist.html',{'total_weight': get_total_weight}, assets = Asset.objects.all())


I suspect there is an issue with the above assetslist method which I am not apparently calling.

Template

{% extends "personal/header.html" %}

{% block content %}

<h1 class='text-center'>This is the full asset list not split by owner</h1></br>

<table class="well table table-striped text-center">
<thead>
<tr class="text-center">
<th class="text-center">Asset ID:</th>
<th class="text-center">Asset Name:</th>
<th class="text-center">Asset Quantity:</th>
<th class="text-center">Asset Weight / kg:</th>
<th class="text-center">Total Weight / kg:</th>
<th class="text-center">Asset Owner:</th>
</tr>
</thead>
<tbody>
<tr class="text-center">
{% for asset in object_list %}
<td>{{ asset.id }}</td>
<td>{{ asset.asset_name }}</td>
<td>{{ asset.asset_quantity }}</td>
<td>{{ asset.asset_weight }}</td>
<td>{{ asset.by_item_weight }}</td>
<td>{{ asset.asset_owner }}</td>



</tr>


{% endfor %}

</tbody>
</table>
<p class="" style="">Total Weight In Stock : {{ get_total_weight }}</p>
<p class="" style="">Total Weight In Stock : {{ assetslist }}</p>



<!-- <table class="well table table-striped text-center">
<thead>
<tr class="text-center">

<th class="text-center"></th>
<th class="text-center"></th>
<th class="text-center"></th>
<th class="text-center"></th>
<th class="text-center">Total Weight / kg:</th>

</tr>
</thead>
<tbody>
<tr class="text-center">
{% for sum_weight in object_list %}

<td></td>
<td></td>
<td></td>
<td></td>
<td>{{ asset.sum_total }}</td>

</tr>


{% endfor %}
</tbody>
</table> -->






<p class="text-center">{% include "sam/includes/backtosam.html" %}</p>



{% endblock %}


Thanks for any input/suggestions.

Further UPDATE:

I have adjusted the view to the below:

from django.core.cache import cache
from django.db.models import Sum, F


def get_total_weight(self):
total_weight = cache.get('total_weight',-1)
if total_weight == -1:
total_weight = Asset.objects.annotate(total_weight=F('asset_quantity')*F('asset_weight')).aggregate(total=Sum('total_weight'))
# this is tested
cache.set('total_weight',total_weight)

return total_weight

render(request,'template_name',{'total_weight': get_total_weight, assets = Asset.objects.all() } )


I am getting errors on the
assets = Asset.objects.all() } )
= sign. SyntaxError: invalid syntax

I presume that render needs to be in its own function?

UPDATE:

I have updated my views and moved the def from the models.py

my views.py file is as below

def total_weight(request):

assets = Asset.objects.all().annotate(
total_weight=ExpressionWrapper(F('asset_quantity') * F('asset_weight'),
output_field=IntegerField() ))

return render(request, 'sam/index.html')


def sum_total(request):

the_total = assets.aggregate(total=Sum('total_weight'))

return render(request, 'sam/assetlist.html')

def index(request):

return render(request, 'sam/index.html')

def by_item_weight(self):
"""This Method gives the total value of the asset. Weight * Qnty"""
total = self.asset_quantity * self.asset_weight
return total

def get_total_weight(self):
total_weight = Asset.objects.filter(by_item_weight__isnull=True).aggregate(Sum('by_item_weight'))
Asset.objects.annotate(total_weight=F('asset_quantity')*F('asset_weight')).aggregate(total=Sum('total_weight'))


def __str__(self):
return '{}'.format(self.asset_name)
return '{}'.format(self.total_weight)


assetlist.html

There is JS below that I tried to implement to resolve this issue which did not work either.

I feel like I am missing something on the assetlist.html in calling the values of the def in the views.py.

{% extends "personal/header.html" %}


{% block content %}

<h1 class='text-center'>This is the full asset list not split by owner</h1></br>




<table id="sum_table" class="well table table-striped text-center">
<thead>
<tr class="text-center titlerow">
<td class="text-center">Asset ID:</td>
<td class="text-center">Asset Name:</td>
<td class="text-center">Asset Quantity:</td>
<td class="text-center">Asset Weight / kg:</td>
<td class="text-center">Total Weight / kg:</td>
<td class="text-center">Asset Owner:</td>
</tr>
</thead>
<tbody>
<tr class="text-center">

{% for asset in object_list %}
<td><a href="/sam/assets/{{ asset.id }}">{{ asset.id }}</></td>
<td>{{ asset.asset_name }}</td>
<td>{{ asset.asset_quantity }}</td>
<td>{{ asset.asset_weight }}</td>
<td class="rowDataSd">{{ asset.by_item_weight }}</td>
<td><a href="/sam/owners/">{{ asset.asset_owner }}</></td>

</tr>

{% endfor %}



<tr class="totalColumn">
<td class=""></td>
<td class=""></td>
<td class=""></td>
<td class=""></td>
<td class="totalCol">Total: {{ asset.get_total_weight }}</td>
<td class=""></td>
</tr>
</tbody>
</table>

<p>Hope this is full ( {{ this_view }} )?</p>

<p class="text-center">{% include "sam/includes/backtosam.html" %}</p>

<!--
<script>
var total = 0;
$('#sum_table tr td.rowDataSd').each(function() {
total += parseInt($(this).text());
});
$('#sum_table td.totalCol').text("total: " + total);
</script>
-->

{% endblock %}


UPDATE - 3rd July 2016

from __future__ import unicode_literals
from django.db import models
from django.db.models import Sum, F, Count
from django.db.models import Max
from django.db.models import ExpressionWrapper
from django.db.models import Aggregate

from django.contrib.auth.models import User

class Asset(models.Model):
asset_name = models.CharField(max_length=30)
asset_quantity = models.IntegerField(default=0)
asset_weight = models.IntegerField(default=0)
total_assets_weight = models.IntegerField(default=0)
asset_owner = models.ForeignKey(
'AssetOwner',
on_delete=models.CASCADE,
) # This should be a Foreign Key Drop down of AssetOwners owner_name.

def by_item_weight(self):
"""This Method gives the total value of the asset. Weight * Qnty"""
total = self.asset_quantity * self.asset_weight
return total

def total_weight(self):

assets = Asset.objects.all().annotate(
total_weight=ExpressionWrapper(F('asset_quantity') * F('asset_weight'),
output_field=IntegerField()))

the_total = assets.aggregate(total=Sum('total_weight'))

return the_total


template

{% extends "personal/header.html" %}


{% block content %}

<h1 class='text-center'>This is the full asset list not split by owner</h1></br>




<table id="sum_table" class="well table table-striped text-center">
<thead>
<tr class="text-center titlerow">
<td class="text-center">Asset ID:</td>
<td class="text-center">Asset Name:</td>
<td class="text-center">Asset Quantity:</td>
<td class="text-center">Asset Weight / kg:</td>
<td class="text-center">Total Weight / kg:</td>
<td class="text-center">Asset Owner:</td>
</tr>
</thead>
<tbody>
<tr class="text-center">

{% for asset in object_list %}
<td><a href="/sam/assets/{{ asset.id }}">{{ asset.id }}</td>
<td>{{ asset.asset_name }}</td>
<td>{{ asset.asset_quantity }}</td>
<td>{{ asset.asset_weight }}</td>
<td class="rowDataSd">{{ asset.by_item_weight}}</td>
<td><a href="/sam/owners/">{{ asset.asset_owner }}</td>



</tr>

{% endfor %}

{% for total in object_list %}

<tr class="totalColumn">
<td class=""></td>
<td class=""></td>
<td class=""></td>
<td class=""></td>
<td class="totalCol">Total: {{ total.total_weight }}</td>
<td class=""></td>
</tr>
</tbody>
</table>
{% endfor %}


<p class="text-center">{% include "sam/includes/backtosam.html" %}</p>



{% endblock %}

Answer

You could use the ExpressionWrapper() (mostly Django 1.8+)

assets = Asset.objects.all().annotate(
    total_weight=ExpressionWrapper(F('asset_quantity') * F('asset_weight'), 
                                   output_field=IntegerField() ))

That should give you the total weight for each object, i.e. quantity times weight.

Now, you should be able to get a sum from all the total_weights.

Edit: Now you can use Aggregate to get the total

assets.aggregate(total=Sum('total_weight'))

{'total': 1234.5678}