[FIXED] DRF : How to sum value from nested object and order by sum value

Issue

I've created serializing nested be using serializers as document

models.py

class Category(models.Model):
    name = models.CharField("Name", "name", max_length=255)
    iconname = models.CharField("Icon Name", "iconname", max_length=255)
    budgetamount = models.DecimalField(
        max_digits=19, decimal_places=2, default=0)
    iconcolor = models.CharField(
        "Icon Color", "iconcolor", default='4294951175', max_length=255)

    def __str__(self):
        return self.name


class DailyExpense(models.Model):
    payee_item_desc = models.CharField(
        "Payee Item Description", "payee_item_desc", max_length=255)
    category = models.ForeignKey(
        Category, related_name='dailyexpense_category', on_delete=models.CASCADE, blank=True, null=True)
    amount = models.DecimalField(max_digits=19, decimal_places=2)
    remarks = models.CharField(
        "Remarks", "remarks", max_length=255, blank=True, null=True)
    tran_date = models.DateTimeField()
    isnotclear = models.BooleanField(default=False)

    def __str__(self):
        return self.payee_item_desc

serializers.py

class DailyExpenseSerializer(serializers.ModelSerializer):
    class Meta:
        model = DailyExpense
        fields = "__all__"


class CategoryWithDailyExpenseSerializer(serializers.ModelSerializer):
    dailyexpense_category = DailyExpenseSerializer(
        source='filtered_dailyexpense_category', many=True, read_only=True)

    class Meta:
        model = Category
        fields = ('id', 'name', 'iconname',
                  'budgetamount', 'iconcolor', 'dailyexpense_category')

views.py

class CategoryWithDailyExpenseViewSet(viewsets.ModelViewSet):
    def get_queryset(self):
        fromDate = parse_datetime(self.request.query_params.get(
            'fromDate') + ' ' + '00:00:00').strftime('%Y-%m-%d %H:%M:%S')
        toDate = parse_datetime(self.request.query_params.get(
            'toDate') + ' ' + '00:00:00').strftime('%Y-%m-%d %H:%M:%S')
        queryset = Category.objects.prefetch_related(
            Prefetch('dailyexpense_category', queryset=DailyExpense.objects.filter(
                tran_date__range=[fromDate, toDate]).order_by('tran_date'), to_attr='filtered_dailyexpense_category')
        )
        return queryset

    # queryset = Category.objects.all().order_by('name')
    serializer_class = CategoryWithDailyExpenseSerializer
    filter_class = CategoryFilter

And the result that I got as below

[
     {
        "id": 2,
        "name": "Foods:Breakfast",
        "iconname": "emoji_food_beverage",
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "dailyexpense_category": [
            {
                "id": 24574,
                "payee_item_desc": "เซเว่น",
                "amount": "-100.00",
                "remarks": "เฟิส",
                "tran_date": "2022-04-01T00:00:00Z",
                "isnotclear": false,
                "category": 2
            },
            {
                "id": 19933,
                "payee_item_desc": "เซเว่น",
                "amount": "-100.00",
                "remarks": "เฟิส",
                "tran_date": "2022-04-03T00:00:00Z",
                "isnotclear": false,
                "category": 2
            }
        ]
    },
     {
        "id": 31,
        "name": "Sport",
        "iconname": "sports_basketball_outlined",
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "dailyexpense_category": [
            {
                "id": 25636,
                "payee_item_desc": "น้ำเกลือแร่",
                "amount": "-20.00",
                "remarks": "",
                "tran_date": "2022-04-01T00:00:00Z",
                "isnotclear": false,
                "category": 31
            },
            {
                "id": 26682,
                "payee_item_desc": "ดีแคทล่อน",
                "amount": "-6700.00",
                "remarks": "",
                "tran_date": "2022-04-01T00:00:00Z",
                "isnotclear": false,
                "category": 31
            },
            {
                "id": 28592,
                "payee_item_desc": "น้ำเกลือแร่",
                "amount": "-20.00",
                "remarks": "",
                "tran_date": "2022-04-02T00:00:00Z",
                "isnotclear": false,
                "category": 31
            }
        ]
    },
]

I wanted to sum amount value in nested objects to show in each parent object as below and order by sum amount

[
     {
        "id": 31,
        "name": "Sport",
        "iconname": "sports_basketball_outlined",
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "sum_amount": "-6740.00",
        "dailyexpense_category": [
            {
                "id": 25636,
                "payee_item_desc": "น้ำเกลือแร่",
                "amount": "-20.00",
                "remarks": "",
                "tran_date": "2022-04-01T00:00:00Z",
                "isnotclear": false,
                "category": 31
            },
            {
                "id": 26682,
                "payee_item_desc": "ดีแคทล่อน",
                "amount": "-6700.00",
                "remarks": "",
                "tran_date": "2022-04-01T00:00:00Z",
                "isnotclear": false,
                "category": 31
            },
            {
                "id": 28592,
                "payee_item_desc": "น้ำเกลือแร่",
                "amount": "-20.00",
                "remarks": "",
                "tran_date": "2022-04-02T00:00:00Z",
                "isnotclear": false,
                "category": 31
            }
        ]
    },
    {
        "id": 2,
        "name": "Foods:Breakfast",
        "iconname": "emoji_food_beverage",
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "sum_amount": "-200.00",
        "dailyexpense_category": [
            {
                "id": 24574,
                "payee_item_desc": "เซเว่น",
                "amount": "-100.00",
                "remarks": "เฟิส",
                "tran_date": "2022-04-01T00:00:00Z",
                "isnotclear": false,
                "category": 2
            },
            {
                "id": 19933,
                "payee_item_desc": "เซเว่น",
                "amount": "-100.00",
                "remarks": "เฟิส",
                "tran_date": "2022-04-03T00:00:00Z",
                "isnotclear": false,
                "category": 2
            }
        ]
    }
]

Is it possible to do this in django rest framework or I have to sum it in front end language?

Solution

You can use the SerializerMethodField in the serializer class.

class CategoryWithDailyExpenseSerializer(serializers.ModelSerializer):
    dailyexpense_category = DailyExpenseSerializer(
        source='filtered_dailyexpense_category', many=True, read_only=True)
    sum_amount = serializers.SerializerMethodField()

    class Meta:
        model = Category
        fields = ('id', 'name', 'iconname', 'sum_amount',
                  'budgetamount', 'iconcolor', 'dailyexpense_category')

    def get_sum_amount(self, obj):
        fromDate = parse_datetime(self.context['request'].query_params.get(
            'fromDate') + ' ' + '00:00:00').strftime('%Y-%m-%d %H:%M:%S')
        toDate = parse_datetime(self.context['request'].query_params.get(
            'toDate') + ' ' + '00:00:00').strftime('%Y-%m-%d %H:%M:%S')
        return obj.dailyexpense_category.aggregate(Sum('amount', filter = Q(dailyexpense_category__tran_date__range=[fromDate, toDate])))['amount__sum']

Or you can add this aggregate functions into the queryset in the serializer.

class CategoryWithDailyExpenseViewSet(viewsets.ModelViewSet):
    def get_queryset(self):
        ...
        queryset = Category.objects.prefetch_related(
            Prefetch('dailyexpense_category', queryset=DailyExpense.objects.filter(
                tran_date__range=[fromDate, toDate]).order_by('tran_date'), to_attr='filtered_dailyexpense_category')
        ).aggregate(Sum('amount', filter = Q(dailyexpense_category__tran_date__range=[fromDate, toDate]))).orderBy('-amount')
        return queryset

Hope it could help.

Answered By - David Lu