CODEWITHSUNDEEP
sqldjangodjango-modelsdjango-queryset
grokpot
grokpot

Reputation: 1492

Nested query with aggregated Foreign Key

I have two models like so:

class Company(models.Model):
    raising = models.DecimalField()

class Investment(models.Model):
    company = models.ForeignKey(Company)
    amount  = models.DecimalField()

Now I want to find all companies where cumulative investments are less than raising

If I added a dynamic property to Company called raised_so_far that aggregated the investment amounts, I could do something like so: 

Company.objects.filter(raised_so_far__lt=F('raising'))

but filtering happens at the SQL level so that's not possible.

So far, this is my best attempt: 

Company.objects.filter(raising__gt=Investment.objects.filter(company=F(outer_company_object)).aggregate(Sum('amount'))['amount__sum'] or 0)

How do I filter on an aggregation of a foreign key using a parent object?

Upvotes: 2

Views: 525

Answers (1)

orokusaki
orokusaki

Reputation: 57148

You just need to annotate the queryset with the total investments (handled via a join), then query using an F object to use the annotated value like a field.

Company.objects.annotate(total_investments=models.Sum('investments__amount')).filter(raising__gt=models.F('total_investments'))

And, you're off to the raises.

Upvotes: 1

Related Questions