[FIXED] Django: Perform GROUP BY over a view queryset

Issue

I need to perform group by over a queryset of objects already filtered by standard django filters in order to filter by how many objects in queryset are related to same foreign key object. My code now (does not work):

class CustomerTicketsViewSet(AbstractViewSet):
    queryset = CustomerTicket.objects.all()
    serializer_class = CustomerTicketSerializer

    def filter_queryset(self, queryset):
        queryset = super().filter_queryset(queryset)

        tr2smp = int(self.request.GET.get('tickets_related_to_same_topic_gte', False))
        if self.action == "list" and tr2smp:
             topics_queryset = queryset.values('topic').annotate(tickets_to_same_mp=Count('topic')) #does not work
             queryset = ..... #filter tickets queryset somehow based on topics_queryset
        return queryset

If I filtered over all objects, I could have used

CustomerTicket.objects.values('topic').annotate(Count('topic'))

but how can I do the same thing with QuerySet?

Solution

Add order_by() to the end of your query:

queryset.values('topic').annotate(Count('topic')).order_by()

An interesting documentation about the usage of values() together with an annotation/aggregation can be found here: https://docs.djangoproject.com/en/4.1/topics/db/aggregation/#values

Answered By - Marco