Django order_by sum of fields

Question

Is it possible to use the django ORM to order a queryset by the sum of two different fields?

For example, I have a model that looks like this:

class Component(models.Model):
    material_cost = CostField()
    labor_cost = CostField()

and I want to do something like this:

component = Component.objects.order_by(F('material_cost') + F('labor_cost'))[0]

But unfortunately, F objects don't seem to work with 'order_by'. Is such a thing possible with django?

http://stackoverflow.com/questions/476017/django-queryset-order-by-method/476033#476033 — Pavel Strakhov, Jul 01 '10 at 18:57
Not quite what I was looking for. That method sums a certain field across all related objects. (In that particular example, it sums the value of all related votes.) I need to sum two fields on the original object. Thus, I can't use the Sum aggregator, unless there's a way to use it that I am not aware of. — So8res, Jul 01 '10 at 19:07

score 20 · Accepted Answer · edited Apr 16 '16 at 09:07

20

You can use extra for this.

Component.objects.extra(
    select={'fieldsum':'material_cost + labor_cost'},
    order_by=('fieldsum',)
)

See the documentation.

edited Apr 16 '16 at 09:07

Saher Ahwal

9,015
32
84
152

answered Jul 01 '10 at 19:34

Daniel Roseman

588,541
66
880
895

That doesn't work for me but `Component.objects.extra(select={'fieldsum':'material_cost + labor_cost'}).order_by=('fieldsum')` does. – Bikash kharel Apr 12 '19 at 14:59
1

These days you should really use `annotate`: `Component.objects.annotate(fieldsum=F('material_cost') + F('labor_cost')).order_by('fieldsum')` – Daniel Roseman Apr 12 '19 at 15:05

score 15 · Answer 2 · answered May 18 '18 at 22:47

I think it's time to provide better answer. Since django team is considering deprecating extra(), it's better to use annotate() with F() expression:

from django.db.models import F

Component.objects.annotate(fieldsum=F('material_cost') + F('labor_cost')).order_by('fieldsum')

see also https://code.djangoproject.com/ticket/25676

score 2 · Answer 3 · edited Jun 09 '14 at 09:14

2

Use extra:

Component.objects.extra(select = {'total_cost' : 'material_cost + labor_cost'},
                                   order_by = ['total_cost',])[0]

edited Jun 09 '14 at 09:14

iMom0

12,493
3
49
61

answered Jul 01 '10 at 19:34

Sam Dolan

31,966
10
88
84

score 0 · Answer 4 · answered Aug 09 '22 at 19:20

0

You can use F() expression directly in order_by now, your suggested code should work:

component = Component.objects.order_by(F('material_cost') + F('labor_cost'))

answered Aug 09 '22 at 19:20

Andriod

1,239
12
18

Django order_by sum of fields

4 Answers4

Linked