This Content is from Stack Overflow. Question asked by bodger
I am struggling with a pretty complex annotation of a QuerySet and I would really appreciate some help.
Here are my models:-
class Player(models.Model): group = models.ForeignKey(Group) class Transaction(models.Model): created = models.DateTimeField() amount = models.DecimalField(decimal_places=2, max_digits=10) player = models.ForeignKey(Player)
Given a particular Group, I can get all the transactions for that Group using:-
But what I need is for each of those transactions to be annotated with the overall balance of the group at the time the transaction was created. So for each transaction, I need to sum all the transactions of the group whose created time was earlier than the transaction’s created date.
To do this, without ending up with tons of database calls, (I think) requires a complex queryset using things like Subquery and OuterRef but I can’t quite figure out the exact logic.
I tried something like this:-
balance = queryset.annotate( balance=Sum( Case( When( date__lte=F("date"), then=F("amount"), ), default=0, output_field=DecimalField(), ) ) ).filter(pk=OuterRef("pk")) queryset.annotate( group_balance=Subquery(balance.values("balance"), output_field=DecimalField()) )
but I know that’s not quite right. I feel like I’m close but it’s driving me mad.
This question is not yet answered, be the first one who answer using the comment. Later the confirmed answer will be published as the solution.
This Question and Answer are collected from stackoverflow and tested by JTuto community, is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.