28

I need to be able to sort on the aggregate of two annotated columns

So I'd like to do something like this:

c = c.annotate(metric=Sum('results__metric'))
c = c.annotate(metric_prior=Sum('results__metric_prior'))
c = c.annotate(variance=F('metric')-F('metric_prior')) #doesn't work, for demonstrative purposes only

and then:

c = c.order_by('variance')

Does anyone know how to accomplish something like the above?

ivanleoncz
  • 9,070
  • 7
  • 57
  • 49
James R
  • 4,571
  • 3
  • 30
  • 45
  • 3
    I believe the Django ORM can't be bent so much, but I'm upvoting your question because I would like to see if anyone can stretch it so far. My advice is to either get your hands dirty using with raw SQL or use a more powerful ORM like SQLAlchemy (you can use it along with the Django ORM, see SQLSoup). – Paulo Scardine Jun 29 '13 at 03:28
  • 3
    this feature is close to making it into a future version of Django https://github.com/django/django/pull/2496 – Anentropic Oct 07 '14 at 16:27

2 Answers2

21

Actually,

c = c.annotate(variance=F('metric')-F('metric_prior'))

works as you would like it to starting with Django 1.8.

Moreover, you can also order by an expression, which means you can just use:

c = c.order_by(F('metric') - F('metric_prior'))

or even just:

c = c.order_by(Sum('results__metric') - Sum('results__metric_prior'))
maciek
  • 3,198
  • 2
  • 26
  • 33
2

Ticket is there for more than 4 years (by 2014), but it can be accomplished with a little .extra() query, like this:

items = MyModel.objects.extra(
    select = {'variance': 'SUM(relatedModel__someField) - SUM(relatedModel__someField)'},
)

Yes, it can be a bit unpredictable with different DBMS. But if you limit the syntax inside extra to very common SQL it should work more or less everywhere.

Ivan Anishchuk
  • 487
  • 3
  • 16