Django queryset count by attribute after initial filtering

Question

Is there a simple, or built-in mechanism in Django to perform a queryset where from the queryset output I will be able to do some statistics based on an specific attribute ?

I read link and I did some initial tests and it seems that annotate and values could solve my issue but...

in my case I do: 1) filtering of Transactions based on transactionStatus attribute THEN 2) I use "values" and "annotate" functions to get quantity per username.

I use queryset like this: queryset = Transaction.objects.filter(transactionStatus=transactionStatus).order_by('-transactionDate').values('owner__username').annotate(count=Count('owner__username'))

Here is my code:

models.py

class Transaction(models.Model): 

    transactionType = models.CharField(max_length=2)
    transactionStatus = models.CharField()
    value = models.DecimalField(max_digits=7, decimal_places=2)
    transactionDate = models.DateField()
    owner = models.ForeignKey(User, related_name="transactions", on_delete=models.CASCADE)

    def __str__(self): 
        return self.transactionDate

serializers.py

class TransactionSTATSerializer(serializers.HyperlinkedModelSerializer): 
    owner__username = serializers.CharField(read_only=True)
    count = serializers.IntegerField(read_only=True)

    class Meta():
        model = Transaction
        fields = (
            'owner__username',
            'count'
        )

views.py

class TransactionStat(generics.ListCreateAPIView): 
    permission_classes = [
        permissions.IsAuthenticated
    ] 

    parser_classes = (MultiPartParser, FormParser)

    serializer_class = TransactionSTATSerializer
     
    name = 'transaction-stat' 

    def get_queryset(self):
        transactionStatus = self.request.query_params.get('transactionStatus', '')
        queryset = Transaction.objects.filter(transactionStatus=transactionStatus, transactionDate__lte=toDate).order_by('-transactionDate').values('owner__username').annotate(count=Count('owner__username'))
            return queryset

the original filtered transactions (before annotate) are as below:

[
    {
        "pk": 261,
        "transactionType": "ch",
        "transactionStatus": "acceptance",
        "transactionDate": "2020-11-01"
        "value": "30.00",
        "owner": {
            "username": "john",
            "first_name": "John",
            "last_name": "Smith",
        }
    },    
    {
        "pk": 262,
        "transactionType": "ch",
        "transactionStatus": "acceptance",
        "transactionDate": "2020-11-01"
        "value": "40.00",
        "owner": {
            "username": "john",
            "first_name": "John",
            "last_name": "Smith",
        }
    }
]

But after returning queryset i get something like this:

[
    {
        "owner__username": "john",
        "count": 1
    },
    {
        "owner__username": "john",
        "count": 1
    }
]

Does this answer your question? [How to query as GROUP BY in django?](https://stackoverflow.com/questions/629551/how-to-query-as-group-by-in-django) — Tom Wojcik, Dec 15 '20 at 13:52

score 0 · Accepted Answer · answered Dec 17 '20 at 12:54

You need to remove the order_by('-transactionDate') in your code

queryset = Transaction.objects.filter(transactionStatus=transactionStatus 
    ).values('owner__username').annotate(count=Count('owner__username'))

As per the Docs, Django ORM will group the query by fields in both values() and order_by()

Also since transactionDate is not part of your final results, you don't need ordering the queryset by transactionDate

Django queryset count by attribute after initial filtering

1 Answers1