CODEWITHSUNDEEP
pythondjangoormdjango-annotate
Chr0nos
Chr0nos

Reputation: 77

How to compute Sum of an aggregate with Django?

I'm currently trying to compute the score of a Survey in SQL side only to be able to order survey by their scores.

My current logic is:

    Survey.objects.annotate(
            answerresponse__realcoef=models.Case(
                models.When(answerresponse__coef__isnull=True,
                            then=models.F('answerresponse__answer__coef')),
                models.When(answerresponse__coef__isnull=False,
                            then=models.F('answerresponse__coef')),
                output_field=models.FloatField(),
            )
            ).annotate(
                answerresponse__realcoef_sum=models.Sum(
                    models.F('answerresponse__realcoef')
                )
            ).annotate(
                points=models.Sum(
                    models.F('answerresponse__realcoef_sum') *
                    models.F('answerresponse__answer__question__points'),
                    output_field=models.IntegerField()
                ),
                maxpoints=models.Sum('sections__question__points')
            )

The database schema is something like:

Survey > Sections > Questions (points) > Answer (coef) > AnswerResponse (coef override)

and I get the following error:

FieldError: Cannot compute Sum('<CombinedExpression: F(answerresponse__realcoef_sum) * F(answerresponse__answer__question__points)>'): '<CombinedExpression: F(answerresponse__realcoef_sum) * F(answerresponse__answer__question__points)>' is an aggregate

which I understand as

That SQL part was not executed yet so you cannot rely on it

Is it possible to achieve that by keeping in the SQL side only?

Upvotes: 2

Views: 5417

Answers (2)

Chr0nos
Chr0nos

Reputation: 77

i was able to find a solution for that: i'm using a subquery to compute the total per survey then annotate into the Survey queryset, the OuterRef method allowed me to have a reference to the parent request pk into the subrequest.

my big error was to think that my annotations were local to my nested field but it was a global aggregation

def mark_bet_scores(surveys_qs: models.QuerySet) -> models.QuerySet:
    """Annotate a bet queryset with the following fields:
    - maxpoints : the amount of possibles points to get on this bet (int)
    - score : the current survey score (from 0 to 100) (float)
    - points : the amount of points gained on this bet (int)
    """
    responses = AnswerResponse.objects \
        .filter(survey__pk=models.OuterRef('pk')) \
        .prefetch_related('answer', 'answer__question') \
        .annotate(
            realcoef=models.Case(
                models.When(coef__isnull=True, then=models.F('answer__coef')),
                models.When(coef__isnull=False, then=models.F('coef'))
            )
        ) \
        .annotate(
            points=models.ExpressionWrapper(
                models.F('realcoef') * models.F('answer__question__points'),
                output_field=models.IntegerField()
            )
        ) \
        .values('survey__pk') \
        .annotate(
            total=models.Sum(models.F('points'))
        ) \
        .values('total')

    # now we need to make the relation between `Survey.answerresponse` and
    # responses
    surveys = surveys_qs \
        .annotate(
            maxpoints=models.Sum('sections__question__points'),
            points=models.Subquery(
                responses,
                output_field=models.IntegerField()
            )
        ) \
        .annotate(
            score=models.Case(
                models.When(maxpoints=0, then=0),
                models.When(maxpoints__gt=0, then=models.ExpressionWrapper(
                    models.F('points') / models.F('maxpoints') * 100,
                    output_field=models.FloatField())
                )
            )
        )
    return surveys

Upvotes: 1

Charnel
Charnel

Reputation: 4432

You can try to first annotate the result of answerresponse__realcoef_sum * answerresponse__answer__question__points with expression wrapper:

.annotate(
    total=ExpressionWrapper(
        F('answerresponse__realcoef_sum') * F('answerresponse__answer__question__points'), output_field=IntegerField())
)

And then aggregate the result by SUM:

.annotate(points=models.Sum('total'))

Upvotes: 2

Related Questions