Nested query with aggregated Foreign Key

Question

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?

orokusaki · Accepted Answer · 2013-07-09 17:36:22Z

1

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.

answered Jul 9, 2013 at 17:36

orokusaki

57.5k61 gold badges189 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

grokpot Over a year ago

Thanks, totally simple and it works.. except for Companies raising money with no Investments (they're left out of the list). This is due to Sum returning None and not 0 though, source

Addison Klinke Over a year ago

What if there was a 3rd level to the hierarchy above Company, call it ParentCompany. Is there an annotate expression that would allow you to sum all investments across all companies in the parent?

orokusaki Over a year ago

@AddisonKlinke you can make a GROUP BY query like this: Investment.objects.values('company__parent__name').annotate(total=models.Sum('amount')). And, you can filter the results on that annotated field - for example:

Investment.objects.values('company__parent__name').annotate(total=models.Sum('amount')).filter(total__gt=models.F('company__raising'))

. Just beware that with a GROUP BY annotated queryset, if you try to do things like output values other than those annotated or listed in values(...) (parent name in this case), you'll get confusing results; and this is normal.

Collectives™ on Stack Overflow

Nested query with aggregated Foreign Key

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related