Django: annotate Sum Case When depending on the st

问题:

In my application i need to get all transactions per day for the last 30 days.

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

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

which is converting gbp or usd to euro but it creates 3 dictionaries with the same day instead of making the sum of them.

This is what it returns: <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=<UTC>), 'amount': 25.0}]>

and this is what i want:

<QuerySet [{'date': datetime.datetime(2018, 6, 19, 0, 0, tzinfo=<UTC>), 'amount': 75.1875}]>

回答1:

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

(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.)

回答2:

We need to aggregate the query set to accomplish what you are trying.

Try using aggregate()

queryset = Transaction.objects.filter(created__gte=last_month, status=Transaction.COMPLETED).extra({"date": "date_trunc('day', created)"}).values('date').aggregate(
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())))

for more info: aggregate()