Is there a way to aggregate a field in Django Rest Framework that will have the summation of one field

Question

Currently I've figured it out on how to aggregate a single column in my serializers.py but the sum of my salary field will go to the total_salary field in my serializer and total_salary isn't in my models. Now my problem is that how can I do something like this in my API below:

"total_salary": 1422.05,
{
        "id": "8c1810d9-b799-46a9-8506-3c18ef0067f8",
        "date": "2019-04-27",
        "virtual_assistant": "Joevie",
        "time_in": "2019-04-27T22:20:13+08:00",
        "time_out": "2019-04-28T05:20:13+08:00",
        "hours": "7.00",
        "client_name": "landmaster",
        "rate": "90.00",
        "salary": "630.00",
        "status": "APPROVED-BY-THE-MANAGER",
        "notes": ""
    },

The existing one is like this.

 {
        "id": "8c1810d9-b799-46a9-8506-3c18ef0067f8",
        "total_salary": 1422.05,
        "date": "2019-04-27",
        "virtual_assistant": "Joevie",
        "time_in": "2019-04-27T22:20:13+08:00",
        "time_out": "2019-04-28T05:20:13+08:00",
        "hours": "7.00",
        "client_name": "landmaster",
        "rate": "90.00",
        "salary": "630.00",
        "status": "APPROVED-BY-THE-MANAGER",
        "notes": ""
 },

Currently a workaround I did is I get the sum of the salary in the ListView as of now, and each time the user will search for a specific month. The computation will change based on the month that the user searched for. Code below.

def get(self, request, *args, **kwargs):
        search = request.GET.get('search')
        user = request.user.staffs.full_name
        current_month = datetime.date.today().month
        current_year = datetime.date.today().year
        payroll_list = VaPayroll.objects.all()
        payroll_data = payroll_list.filter(Q(virtual_assistant=user),
                                           Q(date__month=current_month),
                                           Q(status='APPROVED-BY-THE-MANAGER'))
        total_salary = VaPayroll.objects.filter(Q(virtual_assistant=user), 
                                                Q(date__month=current_month),
                                                Q(status='APPROVED-BY-THE-MANAGER'),
                                                Q(date__year=current_year)).aggregate(Sum('salary'))
        if search:
            payroll_data = payroll_list.filter(Q(virtual_assistant=user),
                                               Q(status='APPROVED-BY-THE-MANAGER'),
                                               Q(date__icontains=search))
            total_salary = VaPayroll.objects.filter(Q(virtual_assistant=user),
                                                    Q(status='APPROVED-BY-THE-MANAGER'),
                                                    Q(date__month=search),
                                                    Q(date__year=current_year)).aggregate(Sum('salary'))
        context = {
            'total_salary': total_salary,
            'payroll_data': payroll_data
        }
        return render(request, self.template_name, context)

This is from my serializers.py

class VaPayrollSerializer(serializers.ModelSerializer):
    total_salary = serializers.SerializerMethodField()

    class Meta:
        model = VaPayroll
        fields = '__all__'

    def get_total_salary(self, obj):
        user = self.context['request'].user.staffs.full_name
        totalsalary = VaPayroll.objects.filter(Q(status='APPROVED-BY-THE-MANAGER'),
                                               Q(virtual_assistant=user),
                                               Q(date__month=datetime.date.today().month),
                                               Q(date__year=datetime.date.today().year)).aggregate(total_salary=Sum('salary'))
        return totalsalary['total_salary']

This is from my modelviewset a get_queryset

def get_queryset(self):
        current_month = datetime.date.today().month
        current_year = datetime.date.today().year
        queryset = VaPayroll.objects.filter(Q(virtual_assistant=self.request.user.staffs.full_name), 
                                            Q(date__month=current_month), 
                                            Q(date__year=current_year))
        return queryset

In my planned use case I expect to have a single column in my API that will have the sum of all the salary, so that I can individually call that field in the frontend without needing to refresh the page to re-compute the total salary. Or is this the right way to do it? Or should I just stick with the View to just re-compute the total salary.

Answer 1

You can not get a json the way you want it because it is not a valid json format. My recommendation is to try something like this:

"salary": {
           "total": 1422.05,
           "detail": {
                      "id": "8c1810d9-b799-46a9-8506-3c18ef0067f8",
                      "date": "2019-04-27",
                      "virtual_assistant": "Joevie",
                      "time_in": "2019-04-27T22:20:13+08:00",
                      "time_out": "2019-04-28T05:20:13+08:00",
                      "hours": "7.00",
                      "client_name": "landmaster",
                      "rate": "90.00",
                      "salary": "630.00",
                      "status": "APPROVED-BY-THE-MANAGER",
                      "notes": ""
             }
      }

For that, you have to instruct your serializer using to_representation() method:

class VaPayrollSerializer(serializers.ModelSerializer):
    class Meta:
        model = VaPayroll
        fields = '__all__'

    def to_representation(self, instance):
        original_representation = super().to_representation(instance)

        representation = {
            'total': self.get_total_salary(instance),
            'detail': original_representation,
        }

        return representation

    def get_total_salary(self, obj):
        user = self.context['request'].user.staffs.full_name
        totalsalary = VaPayroll.objects.filter(Q(status='APPROVED-BY-THE-MANAGER'),
            Q(virtual_assistant=user),
            Q(date__month=datetime.date.today().month),
        Q(date__year=datetime.date.today().year)).aggregate(total_salary=Sum('salary'))

        return totalsalary['total_salary']