Search code examples
djangodjango-ormdjango-annotate

Django annotate(...) taking a dictionary

I have somewhat complex django query with a lot of .annotate:

query = ModuleEngagement.objects.filter(course_id=course_id)\
            .values('username')\
            .annotate(
                videos_overall=Sum(Case(When(entity_type='video', then='count'), output_field=IntegerField()))) \
            .annotate(
                videos_last_week=Sum(Case(When(entity_type='video', created__gt=seven_days_ago, then=1),
                                          output_field=IntegerField()))) \
            .annotate(
                problems_overall=Sum(Case(When(entity_type='problem', then='count'), output_field=IntegerField()))) \
            .annotate(
                problems_last_week=Sum(Case(When(entity_type='problem', created__gt=seven_days_ago, then='count'),
                                            output_field=IntegerField()))) \
            .annotate(
                correct_problems_overall=Sum(Case(When(entity_type='problem', event='completed', then='count'),
                                                  output_field=IntegerField()))) \
            .annotate(
                correct_problems_last_week=Sum(Case(When(entity_type='problem', event='completed',
                                                         created__gt=seven_days_ago, then='count'),
                                                    output_field=IntegerField()))) \
            .annotate(
                problems_attempts_overall=Sum(Case(When(entity_type='problem', event='attempted', then='count'),
                                                   output_field=IntegerField()))) \
            .annotate(
                problems_attempts_last_week=Sum(Case(When(entity_type='problem', event='attempted',
                                                          created__gt=seven_days_ago, then='count'),
                                                     output_field=IntegerField()))) \
            .annotate(
                forum_posts_overall=Sum(Case(When(entity_type='discussion', then='count'),
                                             output_field=IntegerField()))) \
            .annotate(
                forum_posts_last_week=Sum(Case(When(entity_type='discussion', created__gt=seven_days_ago, then='count'),
                                               output_field=IntegerField()))) \
            .annotate(
                date_last_active=Max('created'))

Does annotate accept a dictionary as a parameter so I can move all the annotates into it? If so, what would be the syntax?

Solution

  • Does annotate accept a dictionary as a parameter so I can move all the annotates into it? If so, what would be the syntax?

    You can perform dictionary unpacking. So if you have a dictionary like:

    my_dict = {
    'total_likes': Sum('likes'),
    'total_articles': Sum('articles'),
}

    The you can have a query like:

    MyModel.objects.annotate(**my_dict)

    This is then equivalent to:

    MyModel.objects.annotate(total_likes=Sum('likes'), total_articles=Sum('articles'))

    If you use or more recent, then you can significantly simplify your annotations with:

    ModuleEngagement.objects.filter(course_id=course_id).values('username').annotate(
    videos_overall=Count('pk', filter=Q(entity_type='video')),
    videos_last_week=Count('pk', filter=Q(entity_type='video', created__gt=seven_days_ago)),
    problems_overall=Count('pk', filter=Q(entity_type='problem'),
    problems_last_week=Count('pk', filter=Q(entity_type='problem', created__gt=seven_days_ago)),
    correct_problems_overall=Count('pk', filter=Q(entity_type='problem', event='completed'),
    correct_problems_last_week=Count('pk', filter=Q(entity_type='problem', event='completed', created__gt=seven_days_ago)),
    problems_attempts_overall=Count('pk', filter=Q(entity_type='problem', event='attempted'),
    problems_attempts_last_week=Count('pk', filter=Q(entity_type='problem', event='attempted', created__gt=seven_days_ago)),
    forum_posts__overall=Count('pk', filter=Q(entity_type='discussion'),
    forum_posts__last_week=Count('pk', filter=Q(entity_type='discussion', event='attempted', created__gt=seven_days_ago)),
    date_last_active=Max('created')
).order_by('username')