Django: aggregation-of-aggregation with subquery: cannot-compute-avg-is-an-aggregate

I've been scouting & testing for quite some time now and I'm unable to get anything working with MariaDB.

I have a subquery:

rating_average_by_puzzle_from_completion_plays = Play.objects.filter(completion_id=OuterRef('id')).values('puzzle_id').order_by('puzzle_id').annotate(puzzle_rating_average=Avg('rating')).values('puzzle_rating_average')

where the ratings of all plays corresponding to a given completion are averaged per puzzle_id

Afterwards, I'm trying, for each completion, to average the puzzle_rating_average corresponding to each puzzle_id:

if I do annotate, I end up with :

SequenceCompletion.objects.annotate(rating_avg_from_completion_plays=Subquery(rating_average_by_puzzle_from_completion_plays.annotate(result=Avg('puzzle_rating_average')).order_by().values('result')))


django.core.exceptions.FieldError: Cannot compute Avg('puzzle_rating_average'): 'puzzle_rating_average' is an aggregate

if I do aggregate, I end up with :

SequenceCompletion.objects.annotate(rating_avg_from_completion_plays=Subquery(rating_average_by_puzzle_from_completion_plays.aggregate(result=Avg('puzzle_rating_average')).order_by().values('result')))


ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.

I tried as well:

SequenceCompletion.objects.annotate(rating_avg_from_completion_plays=Avg(Subquery(rating_average_by_puzzle_from_completion_plays)))

and I end up with:

MySQLdb._exceptions.OperationalError: (1242, 'Subquery returns more than 1 row'

Nothing I could find on the documentation or in any post would work, so I'll appreciate any help

Back to Top