annotation of another annotation duplicate query rule

Using Django Using the ORM
1

When I use an annotation in another annotation, query is duplicated like bellow:

groups = StudyGroup.objects.filter(group_filter).select_related('parent').annotate(
        no_members=Count('student', distinct=True),
        point=Sum('student__point__point', filter=point_filter),
        avg_point=ExpressionWrapper(F('point') / F('no_members'), output_field=FloatField()))

In above queryset, Count('student', distinct=True) calculate for no_members and when I use no_members in avg_point annotation again query calculate Count('student', distinct=True) instead of use no_members directly.

COUNT(DISTINCT `users_student`.`user_id`) AS `no_members`,
    SUM(`study_league_point`.`point`) AS `point`,
    (
        SUM(`study_league_point`.`point`) / COUNT(DISTINCT `users_student`.`user_id`)
    ) AS `avg_point`,

In above my SQL code COUNT(DISTINCT `users_student`.`user_id`) is duplicate (SUM(`study_league_point`.`point`) too).

my desired query is like this:

COUNT(DISTINCT `users_student`.`user_id`) AS `no_members`,
    SUM(`study_league_point`.`point`) AS `point`,
    (
        point / no_members
    ) AS `avg_point`,
2

I see where you opened a ticket for this issue and was informed that the ORM will not do what you want here.

What I would suggest would be to look at an EXPLAIN on this query to see if these replicated queries are really a problem. If you’re using PostgreSQL, you’ll find that the query optimizer can internally optimize many situations like this. (However, whether or not any real optimization needs to occur can also depend upon the amount of data involved. If you’re talking about a relatively small amount of data, it probably doesn’t matter at all either way.

3

I think it’s bug in Django in MySQL.
When I use RawSQL query will be optimize and result will be OK.

4

What you need to remember is that SQL is not a procedural language, it’s a declaritive language. The SQL you write is not necessarily what the database engine executes.

It’s only by looking at an EXPLAIN can you really determine what is optimized or not.

It’s also not a bug as this is documented and expected behavior for the construct you are using.

1 Like
5

You are right. It’s OK
Thank you.