I'm trying to display a html table that is populated with sum values from the database. I have sum_amount_purchased and sum_total_price_purchased working fine but I am struggling with sum_amount_sold to get this value I need to query the sales table. Is this possible to do this inside an annotate? I want the sum of all sales for the each currency for that user. Any advice would be much appreciated.
Table Below
{% for transaction in transactions %}
<tr>
<td>{{transaction.currency}}</td>
<td>{{transaction.sum_amount_purchased}}</td>
<td>{{transaction.sum_total_price_purchased }}</td>
<td>{{transaction.sum_amount_sold}}</td>
</tr>
{% endfor %}
Transaction model below
class Transaction(models.Model):
currency = models.CharField(max_length=20)
amount = models.IntegerField()
total_price = models.DecimalField(max_digits=8, decimal_places=2)
date_purchased = models.DateTimeField()
note = models.TextField(default="")
owner = models.ForeignKey(User, on_delete=models.CASCADE)
amount_per_coin = models.DecimalField(max_digits=8, decimal_places=2, editable=False)
def save(self, *args, **kwargs):
self.amount_per_coin = self.total_price / self.amount
super(Transaction, self).save(*args, **kwargs)
def __str__(self):
return str(self.pk)+','+self.currency + ', '+str(self.amount)
def get_absolute_url(self):
return reverse('transaction-detail', kwargs={'pk': self.pk})
Sale model below
class Sale(models.Model):
amount_sold = models.IntegerField()
total_price_sold = models.DecimalField(max_digits=8, decimal_places=2)
date_sold = models.DateTimeField(default=timezone.now)
note = models.TextField(default="")
transaction = models.ForeignKey(Transaction, on_delete=models.CASCADE, related_name="sales")
amount_per_coin_sold = models.DecimalField(max_digits=8, decimal_places=2, editable=False)
def __str__(self):
return str(self.pk)+','+str(self.amount_sold) + ', '+self.note
def save(self, *args, **kwargs):
self.amount_per_coin_sold = self.total_price_sold / self.amount_sold
super(Sale, self).save(*args, **kwargs)
def get_absolute_url(self):
return reverse('sale-detail', kwargs={'pk': self.pk})
View below
@login_required
def portfolio(request):
context = {
'transactions': Transaction.objects.filter(owner=request.user).values('currency').annotate(
sum_amount_purchased=Sum('amount'),
sum_total_price_purchased=Sum('total_price'),
sum_amount_sold=Sum('sales')
),
}
return render(request, 'webapp/portfolio.html', context, {'title': 'Portfolio'})
