In my app, each user has his wallets in which he can save his daily expenses.

How can I get sum of money for each instance of wallet with many expenses saved to it?

I've tried serializers.SerializerMethodField()

from django.db.models import Sum
from django.db.models import Q

class WalletInstanceSerializer(serializers.ModelSerializer):
    owner = serializers.ReadOnlyField(source='owner.id')
    entry = BudgetEntrySerializer(many=True, read_only=True)
    expense_sum = serializers.SerializerMethodField()

    class Meta:
        model = WalletInstance
        fields = '__all__'

    def get_expense_sum(self, obj):

        return WalletInstance.objects.filter(Q(id=obj.id)|Q(entry__entry_type='income')).aggregate(Sum('entry__amount'))['entry__amount__sum']

And this gives me data in the correct format but the sum is wrong, for example

[
    {
        "id": "d458196e-49f1-42db-8bc2-ee1dba438953",
        "owner": 1,
        "entry": [
            {
                "id": 3,
                "owner": 1,
                "title": "dsfdsf",
                "amount": 7,
                "description": "sdfdsf",
                "entry_type": "income",
                "date_added": "2022-08-13",
                "entry_category": {
                    "id": 2,
                    "name": "Transport"
                }
            },
            {
                "id": 4,
                "owner": 1,
                "title": "fesfvsdfgvbtdg",
                "amount": 12,
                "description": "efesf",
                "entry_type": "income",
                "date_added": "2022-08-13",
                "entry_category": {
                    "id": 2,
                    "name": "Transport"
                }
            }
        ],
        "viewable": [
            "[email protected]"
        ],
        "expense_sum": 83,
        "name": "dsdsds",
        "date_added": "2022-08-13"
    }
]

Expense_sum is 83 but amount fields are 7 and 12 so that's equal to 19

How can i get the correct number?

Models

class BudgetEntry(models.Model):
    STATE= [
        ('income','income'),
        ('expenses','expenses'),
    ]
    owner = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='owner_of_entry', on_delete=models.CASCADE)
    title = models.CharField(max_length=20)
    amount = models.IntegerField()
    description = models.CharField(max_length=60, null=True)
    entry_type = models.CharField(max_length=15, choices=STATE, null=True)
    entry_category = models.ForeignKey(Category, null=True, blank=True, related_name='category_of_entry', on_delete=models.SET_NULL)
    date_added = models.DateField(auto_now_add=True)



class WalletInstance(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False, unique=True)
    name = models.CharField(max_length=30, null=True)
    owner = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='owner', on_delete=models.CASCADE)
    viewable = models.ManyToManyField(settings.AUTH_USER_MODEL, related_name='can_view', blank=True)
    entry = models.ManyToManyField(BudgetEntry, related_name='BudgetEntry', blank=True)
    date_added = models.DateField(auto_now_add=True)

CodePudding user response：

I think ORM query for the BudgetEntry is wrong. It should be like the following:

def get_expense_sum(self, obj):
    return obj.entry.filter(entry_type='income').aggregate(Sum('entry__amount'))['entry__amount__sum']

CodePudding user response：

I've figured it out. Firstly I've created @property in my database to get sum of expenses, but this wasn't very useful If I ever wanted to count income so I passed the value arg to the function where the value can be expense/income

class WalletInstance(models.Model):
    id = models.UUIDField(primary_key=True, default=uuid.uuid4, editable=False, unique=True)
    name = models.CharField(max_length=30, null=True)
    owner = models.ForeignKey(settings.AUTH_USER_MODEL, related_name='owner', on_delete=models.CASCADE)
    viewable = models.ManyToManyField(settings.AUTH_USER_MODEL, related_name='can_view', blank=True)
    entry = models.ManyToManyField(BudgetEntry, related_name='BudgetEntry', blank=True)
    date_added = models.DateField(auto_now_add=True)

    def total_amount(self, value):
        queryset = self.entry.filter(entry_type=value).aggregate(
            total_amount=models.Sum('amount'))
        return queryset["total_amount"]

And now I'm calling this function in my serializer instead of counting there

class WalletInstanceSerializer(serializers.ModelSerializer):
    owner = serializers.ReadOnlyField(source='owner.id')
    entry = BudgetEntrySerializer(many=True, read_only=True)
    viewable = serializers.SlugRelatedField(many=True, queryset=get_user_model().objects.all(), slug_field='email')
    expense_sum = serializers.SerializerMethodField()

    class Meta:
        model = WalletInstance
        fields = '__all__'

    def get_expense_sum(self, obj):
        wallet_obj = WalletInstance.objects.get(id=obj.id)
        return wallet_obj.total_amount('expenses')