djangopython-3.xdjango-ormdjango-aggregationdjango-annotate

django annotate weird behavavior (group by model.id)


In my DRF API, I have a view like this

class ActivityAPI(viewsets.ModelViewSet):
    authentication_classes = (SessionAuthentication, TokenAuthentication)
    serializer_class = ActivitySerializer
    queryset = Activity.objects.order_by('-id').all()
    filter_backends = (DjangoFilterBackend,)
    filter_class = ActivityFilter
    filter_fields = ('name', 'ack', 'developer', 'ack_date', 'ack_by', 'verb')    

    def get_count(self, request, *args, **kwargs):
        queryset = self.filter_queryset(self.get_queryset())
        if CASE_1:
            queryset = queryset.values('verb').annotate(count=Count('verb'))
        if CASE_2:
            queryset = Activity.objects.values('verb').annotate(count=Count('verb'))
        return Response(data=queryset)

In CASE_2, I got what i expected which is equivalent to SQL query

SELECT `activity_activity`.`verb`, COUNT(`activity_activity`.`verb`) AS `count` FROM `activity_activity` GROUP BY `activity_activity`.`verb` ORDER BY NULL

But when it's comes to CASE_1, the annotate feature groups the queryset by activity.id , that is

SELECT `activity_activity`.`verb`, COUNT(`activity_activity`.`verb`) AS `count` FROM `activity_activity` GROUP BY `activity_activity`.`id` ORDER BY `activity_activity`.`id` DESC`

NOTE I need url based filtered data for both API and Aggregation


Solution

  • Groups are so small because you have .order_by('-id') on the original queryset. You can fix it by an empty order_by or by sorting only by fields used in group by:

    if CASE_1:
        queryset = queryset.values('verb').annotate(count=Count('verb')).order_by('verb')
    

    Read about Aggregation - Interaction with default ordering or order_by().