Django:根据字段状态注释总和案例 [英] Django: annotate Sum Case When depending on the status of a field
问题描述
在我的应用程序中,我需要获取过去30天每天的所有交易。
In my application i need to get all transactions per day for the last 30 days.
在交易模型中,我有一个货币字段,如果选择的货币是GBP或USD,我想将其转换为欧元。
In transactions model i have a currency field and i want to convert the value in euro if the chosen currency is GBP or USD.
models.py
models.py
class Transaction(TimeMixIn):
COMPLETED = 1
REJECTED = 2
TRANSACTION_STATUS = (
(COMPLETED, _('Completed')),
(REJECTED, _('Rejected')),
)
user = models.ForeignKey(CustomUser)
status = models.SmallIntegerField(choices=TRANSACTION_STATUS, default=COMPLETED)
amount = models.DecimalField(default=0, decimal_places=2, max_digits=7)
currency = models.CharField(max_length=3, choices=Core.CURRENCIES, default=Core.CURRENCY_EUR)
直到现在这是我一直在使用的:
Until now this is what i've been using:
Transaction.objects.filter(created__gte=last_month, status=Transaction.COMPLETED)
.extra({"date": "date_trunc('day', created)"})
.values("date").annotate(amount=Sum("amount"))
包含日期和金额的字典的查询集:
which returns a queryset containing dictionaries with date and amount:
<QuerySet [{'date': datetime.datetime(2018, 6, 19, 0, 0, tzinfo=<UTC>), 'amount': Decimal('75.00')}]>
这就是我现在尝试的内容:
and this is what i tried now:
queryset = Transaction.objects.filter(created__gte=last_month, status=Transaction.COMPLETED).extra({"date": "date_trunc('day', created)"}).values('date').annotate(
amount=Sum(Case(When(currency=Core.CURRENCY_EUR, then='amount'),
When(currency=Core.CURRENCY_USD, then=F('amount') * 0.8662),
When(currency=Core.CURRENCY_GBP, then=F('amount') * 1.1413), default=0, output_field=FloatField()))
)
这会将gbp或usd转换为欧元,但它会在同一天创建3个字典,而不是求和
which is converting gbp or usd to euro but it creates 3 dictionaries with the same day instead of making the sum of them.
这是返回的内容:< QuerySet [{'date':datetime.datetime(2018,6,19,0, 0,tzinfo =< UTC>),'amount':21.655},{'date':datetime.datetime(2018,6,19,0,0,tzinfo =< UTC>),'amount':28.5325} ,{'date':datetime.datetime(2018,6,19,0,0,tzinfo =< UT C>),金额:25.0}]>
这就是我想要的:
< QuerySet [{'date':datetime.datetime(2018,6,19,0,0,tzinfo =< UTC>),'amount':75.1875} ]>
推荐答案
唯一剩下的就是 order_by
。这将(是的,我知道这听起来很奇怪),迫使Django执行 GROUP BY
。因此应将其重写为:
The only thing that remains is an order_by
. This will (yeah, I know that sounds strange), force Django to perform a GROUP BY
. So it should be rewritten to:
queryset = Transaction.objects.filter(
created__gte=last_month,
status=Transaction.COMPLETED
).extra(
{"date": "date_trunc('day', created)"}
).values(
'date'
).annotate(
amount=Sum(Case(
When(currency=Core.CURRENCY_EUR, then='amount'),
When(currency=Core.CURRENCY_USD, then=F('amount') * 0.8662),
When(currency=Core.CURRENCY_GBP, then=F('amount') * 1.1413),
default=0,
output_field=FloatField()
))
).order_by('date')
(我在这里固定了一些格式设置,以使其更易读,尤其是对于小屏幕,但是(如果忽略间距)与问题中的相同,除了 .order_by(..)
当然。)
(I here fixed the formatting a bit to make it more readable, especially for small screens, but it is (if we ignore spacing) the same as in the question, except for .order_by(..)
of course.)
这篇关于Django:根据字段状态注释总和案例的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!