Sumproduct使用Django的聚合 [英] Sumproduct using Django's aggregation

问题描述

问题

• 可以使用 Django的聚合功能来计算sumproduct？

背景

我正在建模一个可以包含多个项目的发票。 Invoice和Item模型之间的多对多关系通过InvoiceItem中间表进行处理。

发票总额 amount_invoiced - 是通过将 unit_price 和数量。以下是我目前正在使用的代码，但我想知道是否有更好的方法来处理这个使用 Django的聚合功能。

当前代码

  class Item（models.Model）：
 item_num = models.SlugField（unique = True）
 description = models.CharField（blank = True，max_length = 100）
 
 
 class InvoiceItem（models.Model） ：
 item = models.ForeignKey（Item）
 invoice = models.ForeignKey（'Invoice'）
 unit_price = models.DecimalField（max_digits = 10，decimal_places = 2）
数量= models.DecimalField（max_digits = 10，decimal_places = 4）
 
 
类发票（models.Model）：
 invoice_num = models.SlugField（max_length = 25）
 invoice_items = models.ManyToManyField（Item，through ='InvoiceItem'）
 
 def _get_amount_invoiced（self）：
 invoice_items = self.invoiceitem_set.all（）
 amount_invoiced = 0 
 invoice_item in invoice_items：
 amount_invoiced + =（invoice_item.unit_price * 
 invoice_item.quantity）
 return amount_invoic ed 
 
 amount_invoiced = property（_get_amount_ntvoiced）
  

解决方案

是的，可以从Django 1.1引入聚合函数。以下是您的模型的解决方案：

  def _get_amount_invoiced（self）：
 self.invoiceitem_set.extra（select = item_total：quantity * unit_price）
）.aggregate（total = Sum（item_total）[total] 
  

然而，强烈建议您将item_total存储在数据库中，因为它可能会受到折扣，税收和其他更改的影响，从而使计算时间变得不切实际甚至不可行。 >

Question

• Is it possible using Django's aggregation capabilities to calculate a sumproduct?

Background

I am modeling an invoice, which can contain multiple items. The many-to-many relationship between the Invoice and Item models is handled through the InvoiceItem intermediary table.

The total amount of the invoice—amount_invoiced—is calculated by summing the product of unit_price and quantity for each item on a given invoice. Below is the code that I'm currently using to accomplish this, but I was wondering if there is a better way to handle this using Django's aggregation capabilities.

Current Code

class Item(models.Model):
    item_num = models.SlugField(unique=True)
    description = models.CharField(blank=True, max_length=100)


class InvoiceItem(models.Model):
    item = models.ForeignKey(Item)
    invoice = models.ForeignKey('Invoice')
    unit_price = models.DecimalField(max_digits=10, decimal_places=2)
    quantity = models.DecimalField(max_digits=10, decimal_places=4)


class Invoice(models.Model):
    invoice_num = models.SlugField(max_length=25)
    invoice_items = models.ManyToManyField(Item,through='InvoiceItem')

    def _get_amount_invoiced(self):
        invoice_items = self.invoiceitem_set.all()
        amount_invoiced = 0
        for invoice_item in invoice_items:
            amount_invoiced += (invoice_item.unit_price *
                invoice_item.quantity)
        return amount_invoiced

    amount_invoiced = property(_get_amount_invoiced)        

解决方案

Yes, it is possible since Django 1.1 where aggregate functions were introduced. Here's a solution for your models:

 def _get_amount_invoiced(self):
     self.invoiceitem_set.extra(select=("item_total": "quantity * unit_price")
                                ).aggregate(total=Sum("item_total")["total"]

It is, however, highly recommended to store item_total in a database, because it may be subject to discounts, taxes and other changes that make calculating it evety time impractical or even impossible.

这篇关于Sumproduct使用Django的聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！