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)
    sum_expense = serializers.SerializerMethodField()

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

    def get_sum_expense(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_expense=Sum('amount', filter=Q(tran_date__range=[fromDate, toDate])))['sum_expense']

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')
        ).annotate(num_daily=Count('dailyexpense_category', filter=Q(dailyexpense_category__tran_date__range=[fromDate, toDate]))).filter(num_daily__gt=0)
        return queryset

    serializer_class = CategoryWithDailyExpenseSerializer
    filter_class = CategoryFilter

And the result that I got as below

[
    {
        "id": 52,
        "name": "KIRIN HOUSE:Internet",
        "iconname": "signal_cellular_alt",
        "sum_expense": -900.0,
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "dailyexpense_category": [
            {
                "id": 25343,
                "payee_item_desc": "ค่าเนต",
                "amount": "-700.00",
                "remarks": "",
                "tran_date": "2022-04-02T00:00:00Z",
                "isnotclear": false,
                "category": 52
            },
            {
                "id": 30166,
                "payee_item_desc": "เนตฟลิก",
                "amount": "-200.00",
                "remarks": "",
                "tran_date": "2022-04-18T00:00:00Z",
                "isnotclear": false,
                "category": 52
            }
        ]
    },
    {
        "id": 50,
        "name": "Gifts",
        "iconname": "card_giftcard",
        "sum_expense": -3000.0,
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "dailyexpense_category": [
            {
                "id": 22298,
                "payee_item_desc": "ให้พ่อ",
                "amount": "-2000.00",
                "remarks": "",
                "tran_date": "2022-04-03T00:00:00Z",
                "isnotclear": false,
                "category": 50
            },
            {
                "id": 30161,
                "payee_item_desc": "ให้พ่อ",
                "amount": "-1000.00",
                "remarks": "",
                "tran_date": "2022-04-15T00:00:00Z",
                "isnotclear": false,
                "category": 50
            }
        ]
    }
]

I wanted to sort sum_expense (custom field from SerializersMethod) to be order maximum sum expense amount as below

[
    {
        "id": 50,
        "name": "Gifts",
        "iconname": "card_giftcard",
        "sum_expense": -3000.0,
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "dailyexpense_category": [
            {
                "id": 22298,
                "payee_item_desc": "ให้พ่อ",
                "amount": "-2000.00",
                "remarks": "",
                "tran_date": "2022-04-03T00:00:00Z",
                "isnotclear": false,
                "category": 50
            },
            {
                "id": 30161,
                "payee_item_desc": "ให้พ่อ",
                "amount": "-1000.00",
                "remarks": "",
                "tran_date": "2022-04-15T00:00:00Z",
                "isnotclear": false,
                "category": 50
            }
        ]
    },
    {
        "id": 52,
        "name": "KIRIN HOUSE:Internet",
        "iconname": "signal_cellular_alt",
        "sum_expense": -900.0,
        "budgetamount": "0.00",
        "iconcolor": "4294951175",
        "dailyexpense_category": [
            {
                "id": 25343,
                "payee_item_desc": "ค่าเนต",
                "amount": "-700.00",
                "remarks": "",
                "tran_date": "2022-04-02T00:00:00Z",
                "isnotclear": false,
                "category": 52
            },
            {
                "id": 30166,
                "payee_item_desc": "เนตฟลิก",
                "amount": "-200.00",
                "remarks": "",
                "tran_date": "2022-04-18T00:00:00Z",
                "isnotclear": false,
                "category": 52
            }
        ]
    }
]

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

CodePudding user response：

I think you can set the ordering_fields in viewset class.

class CategoryWithDailyExpenseViewSet(viewsets.ModelViewSet):
    ...
    serializer_class = CategoryWithDailyExpenseSerializer
    filter_class = CategoryFilter
    ordering_fields = ['-sum_expense']

Or you can add the sort function in list function.

def list(self, request, *args, *kwargs):
    response = super(CategoryWithDailyExpenseViewSet, self).list(request, args, kwargs)
    response.data = sorted(response.data, key=lambda k: (k['sum_expense'], ))
    return response

Hope it could help.