How to aggregate the sum of a class model field in django-CodePudding

I've been attempting unsuccessfully to show the total_net_interest on my dashboard. I think there is a problem with my strategy. Does anyone have a solution to this issue?

Model

class Investment(models.Model):

    PLAN_CHOICES = (
        ("Basic - Daily 2% for 182 Days", "Basic - Daily 2% for 182 Days"),
        ("Premium - Daily 4% for 365 Days", "Premium - Daily 4% for 365 Days"),
    )
    plan = models.CharField(max_length=100, choices=PLAN_CHOICES, null=True)
    principal_amount = models.IntegerField(default=0, null=True)
    investment_id = models.CharField(max_length=10, null=True, blank=True)
    is_active = models.BooleanField(default=False)
    created_at = models.DateTimeField(auto_now=True, null=True)
    due_date = models.DateTimeField(null=True, blank=True)



    def daily_interest(self):
        if self.plan == "Basic - Daily 2% for 182 Days":
            return self.principal_amount * 365 * 0.02/2/182
        else:
            return self.principal_amount * 365 * 0.04/365 
    

    def net_interest(self):
        if self.plan == "Basic - Daily 2% for 182 Days":
            return self.principal_amount * 365 * 0.02/2
        else:
            return self.principal_amount * 365 * 0.04

    def total_net_interest(self):
        return self.Investment.aggregate(total_net_interest=Sum('net_interest'))['total_net_interest']

CodePudding user response：

As pointed out in the comments, you can't aggregate using a model method - however, you could add net_interest to the queryset using annotation and then aggregate the annotated field.

Django .aggregate() on .annotate()

You will likely need to use Case and When to apply your logic from that method.

As an aside, your model PLAN_CHOICES has both values copied, a better way of writing this would be something like this:


PLAN_BASIC = "basic"
PLAN_PREMIUM = "premium"

PLAN_CHOICES = (
        (PLAN_BASIC, "Basic - Daily 2% for 182 Days"),
        (PLAN_PREMIUM, "Premium - Daily 4% for 365 Days")
)

This means that only "basic" and "premium" are stored in the database, but it will still show the verbose strings that you have entered. You can then do something like Investment.PLAN_BASIC or self.PLAN_BASIC, depending on the context, to ensure you aren't hard coding strings in multiple places.

CodePudding user response：

You have to annotate the interests in a queryset to aggregate on this annotations:

from django.db.models import Case, F, FloatField, Q, Sum, When

total = (Investment
         .objects
         .annotate(net=Case(When(Q(plan='Basic - Daily 2% for 182 Days'),
                                 then=F('principal_amount') * 365 * 0.01),
                            default=F('principal_amount') * 365 * 0.04,
                            output_field=FloatField))
         .aggregate(total=Sum('net')))['total']