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": 1,
    "name": "Auto",
    "iconname": "taxi_alert_outlined",
    "budgetamount": "0.00",
    "iconcolor": "4294951175",
    "dailyexpense_category": []
},
{
    "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
        }
    ]
}

]

But I want to show only not blank nested object 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
        }
    ]
}

]

I've tried to create custom field such as sum nested object or count nested object but I want something that more easier to filter only not blank objected.

CodePudding user response：

I think you need to add a filter like the following.

queryset = Category.objects.prefetch_related(
    Prefetch('dailyexpense_category', 
    queryset=DailyExpense.objects.filter(tran_date__range=[fromDate, toDate]).order_by('tran_date'))
).annotate(num_daily=Count('dailyexpense_category', filter = Q('dailyexpense_category__tran_date__range' =[fromDate, toDate]))).filter(num_daily__gt=0)

Hope it could help.