sql - 我正在做一个项目，想知道是否有可能，如果可以，我如何编写查询来为我的表创建上下文

Question

我正在尝试创建一个上下文以呈现到 html 表中。我想用用户购买的每种货币的名称、该货币的当前拥有的总金额、购买的总金额、销售的总金额、指定货币的总当前价值、购买的总价值和销售的总价值来填充表格。我真的很难做到这一点，这可能吗？如果可以的话，我能得到一些建议吗？

下面的函数

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

下面的交易模型

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)

销售型号如下

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)

```

标签： sqldjangomodel

Answer 1

您可以像这样简单地进行查询：

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

并像这样在 html 中呈现表格：

{% 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 %}

现在，HTML 中显示的某些字段在 Transaction 中不存在，我假设它们来自 Sales 模型。比如，sales_amount。我假设它来自销售模型的sold_amount领域。在 Django 中，您需要使用注释来访问这些值。我将使用Sum来获取销售额，并在portfolio视图中使用 queryset 对其进行注释：

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

现在，使用 queryset 的每个对象，我们 shell 获取 的值sales_amount，它将包含与其连接的所有 Sales 对象的总和。我正在使用sales__amount_sold，其中 sales 是反向关系（根据销售模型中配置的相关名称），而amount_sold是字段的名称。然后我们可以在模板中访问它，如下所示：

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