Django custom order_by

Question

I've got a model eg. Car with a foreign key eg. Owner, which may or may not be blank. The Car has a creation_date.

I would like to order these cars by date, but if the car has an owner, the date of birth of the owner must be taken instead of the creation_date of the car.

Is this possible?

score 6 · Answer 1 · answered Oct 02 '11 at 06:50

6

This is possible by falling back to SQL:

Car.objects.filter(...).extra(select={'odate': '''
  if(owner_id,
     (select date_of_birth from owner_table where id=owner_id),
     creation_date
  )
'''}).order_by('odate')

if function is MySQL-specific. In case of SQLite or Postgres you should use case statement.

answered Oct 02 '11 at 06:50

catavaran

44,703
8
98
85

Django is designed to be database independent, so I prefer Timmy's solution. Thanks anyway! – Arthur C May 06 '12 at 18:03

score 5 · Accepted Answer · edited May 23 '17 at 12:30

5

Have a look at this similar question: Good ways to sort a queryset? - Django

You can't use the model's Meta ordering as it only accepts one field

https://docs.djangoproject.com/en/dev/ref/models/options/#ordering

You can't use the query order_by('creation_date', 'birthdate') as it only sorts by birthdate if they have the same creation_date

So, you could write a custom manager to do incorporate a custom sort for you.

import operator
class CarManager(models.Manager):
    def get_query_set(self):
        auths = super(CarManager, self).get_query_set().all().order_by('-creation')
        ordered = sorted(auths, key=operator.attrgetter('birthday'))
        return ordered

class Car(models.Model):
    sorted = CarManager()

so now you can query:

Car.sorted.all()

to get all a queryset of sorted car's

edited May 23 '17 at 12:30

Community

1
1

answered Oct 01 '11 at 10:54

Timmy O'Mahony

53,000
18
155
177

I used this and it worked! I defined a function in my custom manager where I converted the queryset to a list with list(), and then did an insertion sort with car.date as key. get_date is a method in my Car model that returns the birthdate of the owner or the creation date of the car. I later defined car.date as property(get_date). Thanks! – Arthur C May 06 '12 at 17:59
3

Don't forget to be cateful with `list()`: `list(). Force evaluation of a QuerySet by calling list() on it. Be warned, though, that this could have a large memory overhead, because Django will load each element of the list into memory. In contrast, iterating over a QuerySet will take advantage of your database to load data and instantiate objects only as you need them.` [Django doc](https://docs.djangoproject.com/en/dev/ref/models/querysets/#when-querysets-are-evaluated) – MrKsn May 20 '13 at 18:55

score 1 · Answer 3 · edited May 23 '17 at 11:53

1

You could write a method that returns the appropriate date (if car has owner return birthday else return creation_date) and then order your model based on this method.

edited May 23 '17 at 11:53

Community

1
1

answered Oct 01 '11 at 10:52

arie

18,737
5
70
76

This seems like the simplest solution. But the example you give is annotation of the sum, an aggregate function. I want to annotate it with my own function. – Arthur C Oct 08 '11 at 23:41

Django custom order_by

3 Answers3

Linked