I have the following model structure:
Model A:
[no question-relevant fields]
Model B:
linked_to = ForeignKey(A, related_name='involved')
date = DateField()
amount = IntegerField()
what I'm trying to do is getting for each object A all the related model B, from this subset getting the latest one by date, then calculate the sum of all the model B with the same date and the same connected A object.
Right now I'm using this query:
A.objects.all().annotate(latest_date=Max('involved__date'))
This return the correct latest date for each A object, next I'm trying:
A.objects.all().annotate(latest_date=Max('involved__date')).annotate(total=Sum(Case(When(involved__date=F('latest_date'), then='involved__amount'))))
But I'm getting a
AttributeError: 'Case' object has no attribute 'name'.
I've used multiple annotate before, without any issue, the difference is that in the past I always used previous annotated values coming from existing field, never from a Max/Min, so I'm not sure if this makes the difference.
Django version is 1.8
I cannot use any raw SQL here and I know this can be easily done in 2 steps, but at this point is more a personal curiosity of understanding how this works.