I'm trying to build a Django query that will sum multiple categories in to one distinct category

user_13543418 0
Viewed 37

I have a model called Actuals with a field called category which is unique, and another model called Budget which is a many to many field in the Actuals Model. A user can select a unique category in budget and select it in actuals so there can be many actuals to a budget. I am trying to create a query that will group and Sum 'transaction_amount' by category in Actuals model.

class Actuals(models.Model):
     category = models.ForeignKey(Category,on_delete=models.CASCADE)
     date = models.DateTimeField(auto_now_add=False)
     transactions_amount = models.IntegerField()
     vendor = models.CharField(max_length = 255,default="")
     details = models.CharField(max_length = 255)
     budget = models.ManyToManyField('budget')

     def __str__(self):
         return self.category.category_feild

This is the query that I currently have. However it still gives me multiple categories

lub = Actuals.objects.filter(category__income_or_expense = 'Expense', date__year = "2022" ,date__month = "01").values('category__category_feild','date').order_by('category__category_feild').annotate(total_actuals = Sum('transactions_amount')).annotate(total_budget = Sum('budget__budget_amt'))

This is the output. There should only be one line for "Fun" and one line for "Paycheck".

<QuerySet [<Actuals: Fun>, <Actuals: Fun>, <Actuals: Paycheck>, <Actuals: Paycheck>]>
django-queryset django-orm django-models django python
1 Answers

annotate method only adds another attribute to the objects returned in the queryset. If you want to get a single object as a result you should use the aggregate queryset method:

lub = Actuals.objects.filter(category__income_or_expense='Expense', date__year="2022" ,date__month="01").order_by('category__category_feild').aggregate(total_actuals=Sum('transactions_amount'), total_budget=Sum('budget__budget_amt'))

To get the result values use:

total_actuals = lub['total_actuals']
total_budget = lub['total_budget']

This code is not tested, so let me know if it works.

Also is category__category_feild a typo, or?

PS., if you're wondering why I didn't use values, see this antipattern

user_88108650
Related