1

I have to build a field from an annotation, the field is a score which is equal to the division of two other annotations which are based on different Querysets Expressions.

annotated_queryset = Influencer.objects.annotate(
    score_sum=Sum('submission__answer__decisions__score'),
    submission_count=Count('submission',
        filter=Q(
            submission__status__in=Submission.ANSWERED_STATUS
        )
    )
).annotate(rate=F('score_sum') / F('submission_count'))

This snippet shows how i get my annotation.

Now i'd love to be able to do the following :

annotated_queryset.update(acceptation_rate=F('rate'))

But I get a FieldError: Aggregate functions are not allowed in this query

My only solutions is to use the ugly and expensive for loop :

for each in annotated_queryset:
    each.acceptation_rate = each.rate
    each.save()

My questions are :

  • Why can't I use the annotate + update form ?
  • Is there any better way than the for loop to do this ? What would it be ?

Edit, Using Subqueries :

annotated_queryset = Influencer.objects.annotate(
    score_sum=Sum('submission__answer__score'),
    submission_count=Count('submission',
        filter=Q(
            submission__status__in=Submission.ANSWERED_STATUS
        )
    )
).annotate(
    rate=ExpressionWrapper(
        F('score_sum') / F('submission_count'),
        output_field=FloatField()
    )
)[:1]

Influencer.objects.update(acceptation_rate=Subquery(annotated_queryset))
Taek
  • 1,004
  • 7
  • 20
  • Because a database typically does not allow when you JOIN together, but by a subquery, you can get this working. – Willem Van Onsem Jul 31 '18 at 13:06
  • See here: https://stackoverflow.com/a/50134728/67579 – Willem Van Onsem Jul 31 '18 at 13:08
  • I tried using subqueries but I now get an obscure `SQL Exception : LINE 1: ...T "acceptation_rate" = (SELECT (SUM(U4."score") / COUNT(U1."... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.` Should i update my question with the code i used to get this ? – Taek Jul 31 '18 at 13:19
  • Can you share the subquery approach? – Willem Van Onsem Jul 31 '18 at 13:20
  • 1
    You probably need to add `[:1]` at the end of the `annotated_queryset`, since here you do not really annotated the *entire* table, but each *row*. – Willem Van Onsem Jul 31 '18 at 14:50
  • I have the same error whit the [:1]. While searching i found several other issues and updated my question to reflect the changes. But i'm still stuck with the same SQL exception. Thanks for your help anyway ! – Taek Jul 31 '18 at 15:22

0 Answers0