CODEWITHSUNDEEP
djangodjango-subquery
Niladry Kar
Niladry Kar

Reputation: 1203

How to perform a double step Subqueries in Django?

I have the below models:

class Group(models.Model):
    group_name = models.CharField(max_length=32)

class Ledger(models.Model):
    ledger_name = models.CharField(max_length=32)
    group_name  = models.ForeignKey(Group,on_delete=models.CASCADE,null=True,related_name='ledgergroups')

class Journal(models.Model):
    By              = models.ForeignKey(Ledger,on_delete=models.CASCADE,related_name='Debitledgers')
    To              = models.ForeignKey(Ledger,on_delete=models.CASCADE,related_name='Creditledgers')
    Debit           = models.DecimalField(max_digits=20,decimal_places=2,default=0)
    Credit          = models.DecimalField(max_digits=20,decimal_places=2,default=0)

As you can see the Journal model is related with the Ledger models with a Foreignkey relation which is further related with Group model.

My scenario is kind of complex.

I want to filter the Group objects and their balances (Balances are the difference between their total Debit and their total Credit).

I want to filter the total Group names and the subtraction of total Debit and total Credit..(Debit and Credit are the fields of Journal model).

Can anyone help me to figure out the above.

I have tried Subqueries before in Django but haven't done a two step Subquery in Django.

Any solution will be helpful.

Thank you

Upvotes: 2

Views: 446

Answers (2)

rishabh singh
rishabh singh

Reputation: 165

try this 

from django.db.models import Subquery
>>> users = User.objects.all()
>>> UserParent.objects.filter(user_id__in=Subquery(users.values('id')))
<QuerySet [<UserParent: UserParent object (2)>, <UserParent: UserParent object (5)>, <UserParent: UserParent object (8)>]>

Upvotes: 0

Iain Shelvington
Iain Shelvington

Reputation: 32244

You can use annotations to calculate sums, averages, counts, etc. These annotations can then be used to filter on

from django.db.models import Sum, F    

groups_with_negative_balance = Group.objects.annotate(
    total_debt=Sum('ledgergroups__Debitledgers__Debit'),
    total_credit=Sum('ledgergroups__Creditledgers__Credit'),
).annotate(
    balance=F('total_credit') - F('total_debt')
).filter(
    balance__lt=0
)

Upvotes: 3

Related Questions