How to return a group by query via Django REST API?-CodePudding

I have a Django REST API which works perfectly when I want to query some data from the database. Here I have an example:

views.py

class ProductListAPIView(generics.ListAPIView):
    
    def get_queryset(self):
           
        # Collect data from products table and filter it
        queryset = Product.objects.filter(name="Ferrari", date__lte="2022-08-01") # <- Substitute with line below
     
        return queryset
    
    serializer_class       = ProductSerializer
    authentication_classes = [authentication.SessionAuthentication, authentication.TokenAuthentication]
    permission_classes     = [IsOwnerPermission]

serializers.py

from rest_framework import serializers
from insert_data.models import Product

class ProductSerializer(serializers.ModelSerializer):

    class Meta:
        model = Product
        fields = ["name","category","date","price"]

The output is what I expect i.e. a json response with the content of the database.

The problems arise when I try to group the queryset in order to return the average price of a product. By doing some research in the internet I noticed that I just need to substitute the line of code in the views.py script with this:

Product.objects.filter(name=params['name'], date__lte=query.date).values('name','category').annotate(price_average=Avg('price')).order_by()

I am pretty confident that this does the work, however the API returns an error which I do not know how to fix:

AttributeError: 'int' object has no attribute 'pk'

I totally have no idea what this refers to. Would you be able to suggest a smart and elegant way to return the average price of a product after a group by operation via the REST API?

CodePudding user response：

Is category a ForeignKey to a model? I think the problem is with this field in that case.

I believe that when you call .values() you get the id of foreignKey-fields and you can't access that field as an object anymore.

So REST Framework is trying to access the property pk on what it thinks is an instance of Category.

To fix this you can create a custom serializer where category is an integer instead and you've added price_average. You have to check the field types, I don't know what types you have.

class ProductPriceAverageSerializer(serializers.Serializer):
    category = serializers.IntegerField()
    price_average = serializers.FloatField()