How can I return several aggregates based on a condition within a queryset?-CodePudding

So I have the following model which contains different booleans and one amount field. I now want to render the overall sum amount for each boolean when true (only one boolean will be true at once always).

class UserTransaction(models.Model):
    """
    A table to store transactions between a user and Farena
    """
    user = models.ForeignKey(User, on_delete=models.CASCADE)
    offer = models.ForeignKey(Offer, on_delete=models.CASCADE, blank=True, null=True)
    created_at = models.DateTimeField(auto_now_add=True)
    amount = models.FloatField()
    is_deposit = models.BooleanField(default=False)
    is_withdrawal = models.BooleanField(default=False)
    is_interest = models.BooleanField(default=False)
    is_investment = models.BooleanField(default=False)
    is_return = models.BooleanField(default=False)

    def __str__(self):
        return f'{self.user.first_name} {self.user.last_name} transacted {self.amount}'

I tried to build it with annotate but that would add fields for each instance which wouldn't solve my issue as far as I understand it. Additionally using aggregate I couldn't implement a condition and also wouldn't know how to access the aggregated value in the template?

CodePudding user response：

You can aggregate over all the fields, so:

UserTransaction.objects.aggregate(
    total_deposit=Sum('is_deposit'),
    total_withdrawal=Sum('is_withdrawal'),
    total_interest=Sum('is_interest'),
    total_investment=Sum('is_investment'),
    total_return=Sum('is_return')
)

or for some databases where a bool is not implemented as an integer:

UserTransaction.objects.aggregate(
    total_deposit=Count('pk', filter=Q(is_deposit=True)),
    total_withdrawal=Count('pk', filter=Q(is_withdrawal=True)),
    total_interest=Count('pk', filter=Q(is_interest=True)),
    total_investment=Count('pk', filter=Q(is_investment=True)),
    total_return=Count('pk', filter=Q(is_return=True))
)

This will return a dictionary that looks like:

{
    'total_deposit': 14,
    'total_withdrawal': 25,
    'total_interest': 13,
    'total_investment': 2
    'total_return': 17
}