0

I am performing the following query with a few annotations:

    (AwardIssueProcess.objects.filter(grant__client=client, completed=completed_status)
        .annotate(total_awarded=Sum('award__awardissuedactivity__units_awarded'),
                  num_accepted=Count(Case(When(award__accepted=True, then=1))),
                  num_rejected=Count(Case(When(award__accepted=False, then=1))),
                  num_unaccepted=Count(Case(When(award__accepted=None, then=1)))))

However, the num_unaccepted yields incorrect values. Firstly, if I have awards the number is sometimes double what I expect. But if I remove 

total_awarded=Sum('award__awardissuedactivity__units_awarded')

from the annotation, then the doubling problem goes away.

Secondly, the num_unaccepted has a value of 1 if there are no awards. But when there are awards, the value is correct (but not is all cases due to the doubling problem I mentioned previously). In this second issue, I suspect that this may be because it is evaluating the award to be None, but what I really want is for it to check if the accepted field is None. And then if the award doesn't exists, just don't count it. The accepted field is a NullBooleanField.

How should I be writing this differently to solve these two problems with num_unaccepted?

EDIT

I removed total_awarded=Sum('award__awardissuedactivity__units_awarded') from the annotation and created a separate function to get the total_awarded amount that I need. This addresses my first problem, but the second issue still remains.

Daniel Holmes
  • 1,952
  • 2
  • 17
  • 28
  • 3
    That is the expected behavior: if you `JOIN` on two independent tables, you "multiply" the rows. So if there are two `awardissuedactivity` elements, then every `award` row will be doubled. – Willem Van Onsem Jun 05 '18 at 05:55
  • I see, thanks! However, the `num_unaccepted` being 1 is still an issue when there are no awards. – Daniel Holmes Jun 05 '18 at 09:00
  • Well in that case there is probably for that `AwardIssueProcess` item either no or one `awardissuedactivity`. – Willem Van Onsem Jun 05 '18 at 09:06
  • I updated and removed any reference to `awardissuedactivity` in the annotate (see my edited question). So how can it still be an issue? – Daniel Holmes Jun 05 '18 at 10:10

0 Answers0