CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-viewsdjango-orm
Maqbool Thoufeeq T
Maqbool Thoufeeq T

Reputation: 338

How to combine two django model objects values in a single queryset

I need a queryset which should be combination of two model objects. Please look the code

models.py

class BudgetSubcategory(models.Model):
    sub_category = models.CharField(
        _("Sub Category"), max_length=255, default=False)
    sub_budget = models.DecimalField(
        _("Budget"), max_digits=15, decimal_places=2, default=Decimal('0.0000'))

class BudgetActivity(models.Model):
    sub_category = models.ForeignKey(BudgetSubcategory, on_delete=models.CASCADE)
    activity = models.CharField(
        _("Activity"), max_length=255, default=False)
    cost = models.DecimalField(
        _("Cost"), max_digits=15, decimal_places=2, default=Decimal('0.0000'))

in views I'm calling

qs1 = BudgetActivity.objects.values('activity').annotate(budget_cost=Sum('cost'))
qs2 = BudgetSubcategory.objects.values('sub_category').annotate(
      actual_cost=Sum('sub_budget'))

q1 will be

[{'activity': 'activity1', 'budget_cost': Decimal('300.00')},
 {'activity': 'activity2', 'budget_cost': Decimal('2000.00')}]

q2 will be

[{'sub_category': 'sub_category1', 'actual_cost': Decimal('500.00')}, 
{'sub_category': 'sub_category2', 'actual_cost': Decimal('2300.00')}]

But what I need is

[{'sub_category': 'sub_category1', 'actual_cost': Decimal('500.00'),
 'budget_cost': Decimal('300.00')}, {'sub_category': 'sub_category2',
 'actual_cost': Decimal('2300.00'),'budget_cost': Decimal('2000.00')}]

Please Help me regarding this issue

Upvotes: 1

Views: 703

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476709

You can make an annotation on a related object by using double underscores (__):

from django.db.models import F, Sum

qs2 = BudgetSubcategory.objects.values(
    'sub_category'
).annotate(
    actual_cost=F('sub_budget'),
    budget_cost=Sum('budgetactivity__cost')
)

It however might be better to annotate the BudgetSubcategory without using .values(..), since then you still work with BugetSubcategorys, and thus you still have the "logic" that is encapsulated in the model:

from django.db.models import F, Sum

qs2 = BudgetSubcategory.objects.annotate(
    actual_cost=F('sub_budget'),
    budget_cost=Sum('budgetactivity__cost')
)

Upvotes: 2

Related Questions