I'm doing a project and am wondering is it possible and if so how could I write a queries to create the context for my table

Question

I'm trying to create a context to render into a html table. I want to populate the table with the name of each currency the user has purchased,total current amount owned of that currency, total purchased amount, total sold amount, total current value of specified currency, total purchased value, and total sold value. I,m really struggling to do this is it possible? and if so could i get some advice on how

Function Below

def portfolio(request):    

    count = Transaction.objects.filter(owner=request.user).values('currency').distinct(),count

    context = {        
    }

    return render(request, 'webapp/portfolio.html', context, {'title': 'Portfolio'})

Html Table below

<table class="table">
            <thead>
            <tr>
                <th scope="col">Coin</th>
                <th scope="col">Current</th>
                <th scope="col">Purchased</th>
                <th scope="col">Sold</th>
                <th scope="col">Current Value</th>
                <th scope="col">Purchased Value</th>
                <th scope="col">Sold Value</th>
            </tr>
            </thead>
            <tbody>
            {% for total_transaction in total_transaction %}
            <tr>
                <td>{{total_transaction.currency}}</td>
                <td>{{total_transaction.current_amount}}</td>
                <td>{{total_transaction.purchased_amount}}</td>
                <td>{{total_transaction.sold_amount}}</td>
                <td>{{total_transaction.current_value}}</td>
                <td>{{total_transaction.purchased_value}}</td>
                <td>{{total_transaction.sold_value}}</td>                
            </tr>
            {% endfor %}
            </tbody>
        </table>

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})

    @property
    def coin_value(self):
        try:
            current_price = requests.get("https://min-api.cryptocompare.com/data/price?fsym="+self.currency+"&tsyms=EUR")
            price = json.loads(current_price.content)
            return price["EUR"]
        except:
            return 0


    @property
    def total_value(self):
        value = self.coin_value * self.amount
        return round(value, 2)

    @property
    def profit_loss(self):
        value = float(self.total_value) - float(self.total_price)
        return round(value, 2)

    @property
    def profit_loss_percent(self):
        value = ((float(self.total_value) - float(self.total_price))/self.total_value)*100
        return round(value, 1)

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})

    @property
    def profit_loss(self):
        return (self.amount_per_coin_sold - self.transaction.amount_per_coin) * self.amount_sold

    @property
    def profit_loss_percent(self):
        value = ((self.total_price_sold - (self.transaction.amount_per_coin * self.amount_sold))/self.total_price_sold) * 100
        return round(value, 1)

```

Answer 1

You can simply do the query like this:

transactions = Transaction.objects.filter(owner=request.user)
context = {  
    'total_transactions' :  transactions   
}
return render(request, 'webapp/portfolio.html', context)

And render the table in html like this:

{% for total_transaction in total_transactions %}
    <td>{{total_transaction.currency}}</td>  // You can access Transaction model's fields or properties like this
    <td>{{total_transaction.amount}}</td>
    // and so on
{% endfor %}

Now, some of the fields shown in HTML does not exist in Transaction, I am assuming they come from Sales model. Such as, sales_amount. I am assuming it comes from Sale Model's sold_amount field. In Django, you need to use annotation to access those values. I am going to use Sum to get the sales amount, and annotate it with queryset in portfolio view:

def portfolio(request):
    transactions = Transaction.objects.filter(owner=request.user).annotate(sales_amount=Sum('sales__amount_sold'))
    # rest of the code same as above

Now, with queryset's each object, we shell get the value of sales_amount, which will contain sum of all the Sales objects connected to it. I am using sales__amount_sold, where sales is the reverse relation(as per related name configured in Sale Model) and amount_sold is the name of the field .Then we can access it in template like this:

{% for total_transaction in total_transactions %}
    // rest of the code same as above
    <td>{{total_transaction.sales_amount}}</td>
{% endfor %}