我有一個包含四個重要欄位的模型，我想查詢此模型以獲取每個月的值總和，但這些值的計算方式基于其貨幣不同。因此，例如，如果貨幣 = 美元，則公式為 a * b / c，如果為英鎊，則為 a * b * c。這是我迄今為止所做的；
# model
class Permit(models.Model):
CONVERSION = [
(decimal.Decimal('1.0000'), '1.0000'),
(decimal.Decimal('0.0006'), '0.0006'),
(decimal.Decimal('0.0007'), '0.0007'),
(decimal.Decimal('0.0010'), '0.0010'),
(decimal.Decimal('0.0015'), '0.0015'),
]
UNITS = [
('m2', 'm2'),
('m3', 'm3'),
]
CURRENCY = [
('USD', 'USD'),
('GBP', 'GBP'),
('CFA', 'CFA'),
('Euro', 'Euro'),
]
product = models.ForeignKey(Product, on_delete=models.CASCADE)
specie = models.ForeignKey(Specie, on_delete=models.CASCADE)
grade = models.ForeignKey(Grade, on_delete=models.CASCADE)
info = models.ForeignKey(Info, on_delete=models.CASCADE)
# volume
volume = models.DecimalField(max_digits=19, decimal_places=3)
conversion_factor = models.DecimalField(
choices=CONVERSION, max_digits=5, decimal_places=4)
units = models.CharField(choices=UNITS, max_length=2, default='m3')
unit_price = models.DecimalField(max_digits=19, decimal_places=3)
currency = models.CharField(choices=CURRENCY, max_length=5)
ex_rate = models.DecimalField(
max_digits=19, decimal_places=7, verbose_name='Exchange Rate')
def __str__(self):
return str(self.info.exporter)
@property
def euro_value(self):
if self.volume and self.unit_price and self.ex_rate != None:
if self.currency == 'USD':
ev = self.volume * self.unit_price
ev = ev / self.ex_rate
ev = round(ev, 2)
else:
ev = self.volume * self.unit_price * self.ex_rate
ev = round(ev, 2)
else:
ev = 0
return ev
# the query
q1 = Permit.objects.filter(info__date_of_transaction__year=year
).filter(currency='USD'
).values(
'info__date_of_transaction__month').annotate(
value=Sum(F('volume') * F('unit_price') / F('ex_rate'))
).order_by('info__date_of_transaction__month')
q2 = Permit.objects.filter(info__date_of_transaction__year=year
).exclude(currency='USD'
).values(
'info__date_of_transaction__month').annotate(
value=Sum(F('volume') * F('unit_price') * F('ex_rate'))
).order_by('info__date_of_transaction__month')
# the answer i am expecting
{"labels": ["August", "September"], "data": ["123562.37", "10189.25"]}
現在的問題是，我如何執行查詢？
uj5u.com熱心網友回復：
您可以使用條件運算式Case并When在注釋中根據條件執行不同的邏輯
from django.db.models import Case, When, F
Permit.objects.annotate(
value=Case(
When(currency='USD', then=F('volume') * F('unit_price') / F('ex_rate')),
When(currency='GBP', then=F('volume') * F('unit_price') * F('ex_rate'))
)
)
這不包括您在查詢中的分組，但想法應該是相同的
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/327160.html
標籤：姜戈 django-models