2

My model consists of a Portfolio, a Holding, and a Company. Each Portfolio has many Holdings, and each Holding is of a single Company (a Company may be connected to many Holdings).

Portfolio -< Holding >- Company

I'd like the Portfolio query to return the sum of the product of the number of Holdings in the Portfolio, and the value of the Company.

Simplified model:

class Portfolio(model):
   some fields

class Company(model):
   closing = models.DecimalField(max_digits=10, decimal_places=2)

class Holding(model):
    portfolio = models.ForeignKey(Portfolio)
    company = models.ForeignKey(Company)
    num_shares = models.IntegerField(default=0)

I'd like to be able to query:

Portfolio.objects.some_function()

and have each row annotated with the value of the Portfolio, where the value is equal to the sum of the product of the related Company.closing, and Holding.num_shares. ie something like:

annotate(value=Sum('holding__num_shares * company__closing'))

I'd also like to obtain a summary row, which contains the sum of the values of all of a user's Portfolios, and a count of the number of holdings. ie something like:

aggregate(Sum('holding__num_shares * company__closing'), Count('holding__num_shares'))

I would like to do have a similar summary row for a single Portfolio, which would be the sum of the values of each holding, and a count of the total number of holdings in the portfolio.

I managed to get part of the way there using extra:

    return self.extra(
        select={
            'value': 'select sum(h.num_shares * c.closing) from portfolio_holding h '
                'inner join portfolio_company as c on h.company_id = c.id '
                'where h.portfolio_id = portfolio_portfolio.id'
        }).annotate(Count('holding'))

but this is pretty ugly, and extra seems to be frowned upon, for obvious reasons.

My question is: is there a more Djangoistic way to summarise and annotate queries based on multiple fields, and across related tables?

These two options seem to move in the right direction:

Portfolio.objects.annotate(Sum('holding__company__closing'))

(ie this demonstrates annotation/aggregation over a field in a related table)

Holding.objects.annotate(Sum('id', field='num_shares * id'))

(this demonstrates annotation/aggregation over the product of two fields)

but if I attempt to combine them: eg

Portfolio.objects.annotate(Sum('id', field='holding__company__closing * holding__num_shares'))

I get an error: "No such column 'holding__company__closing'.

So far I've looked at the following related questions, but none of them seem to capture this precise problem: Annotating django QuerySet with values from related table Product of two fields annotation

Do I just need to bite the bullet and use raw / extra? I'm hoping that Django ORM will prove the exception to the rule that ORMs really only work as designed for simple queries / models, and anything beyond the most basic ones require either seriously gnarly tap-dancing, or stepping out of the abstraction, which somewhat defeats the purpose...

Thanks in advance!

Community
  • 1
  • 1
River Satya
  • 1,059
  • 12
  • 20
  • You can use `F` objects to use different fields on the same query, but I'm not sure they are supported yet for aggregations (see [ticket 14030]()https://code.djangoproject.com/ticket/14030)) – Jorge Leitao May 29 '14 at 05:37

0 Answers0