How to sort a Django QuerySet by (field, custom function, field)

Question

I am looking for getting a QuerySet that is sorted by field1, function, field2.

The model:

class Task(models.Model):
    issue_id = models.CharField(max_length=20, unique=True)
    title = models.CharField(max_length=100)
    priority_id = models.IntegerField(blank=True, null=True)
    created_date = models.DateTimeField(auto_now_add=True)

    def due_date(self):
        ...
        return ageing

I'm looking for something like:

taskList = Task.objects.all().order_by('priority_id', ***duedate***, 'title')

Obviously, you can't sort a queryset by custom function. Any advise?

lanzz · Accepted Answer · 2014-02-20T11:31:52.340

Since the actual sorting happens in the database, which does not speak Python, you cannot use a Python function for ordering. You will need to implement your due date logic in an SQL expression, as an Queryset.extra(select={...}) calculated field, something along the lines of:

due_date_expr = '(implementation of your logic in SQL)'
taskList = Task.objects.all().extra(select={'due_date': due_date_expr}).order_by('priority_id', 'due_date', 'title')

If your logic is too complicated, you might need to implement it as a stored procedure in your database.

Alternatively, if your data set is very small (say, tens to a few hundred records), you can fetch the entire result set in a list and sort it post-factum:

taskList = list(Task.objects.all())
taskList.sort(cmp=comparison_function) // or .sort(key=key_function)

Nice answer. So it should either be dealt with in SQL end or in Python end. — killua8p, Feb 20 '14 at 12:08

score 2 · Answer 2 · edited Dec 22 '19 at 14:14

2

The answer by @lanzz, even though seems correct, didn't work for me but this answer from another thread did the magic for me:

https://stackoverflow.com/a/37648265/6420686

from django.db.models import Case, When

ids = [list of ids]
preserved = Case(*[When(id=pk, then=pos) for pos, pk in enumerate(ids)])
filtered_users = User.objects \
    .filter(id__in=ids) \
    .order_by(preserved)

edited Dec 22 '19 at 14:14

Matus Dubrava

13,637
2
38
54

answered Dec 22 '19 at 13:52

Arpit Agarwal

111
9

arocks · Answer 3 · 2014-02-20T12:31:14.490

0

You can use sort in Python if the queryset is not too large:

ordered = sorted(Task.objects.all(), key=lambda o: (o.priority_id, o.due_date(), o.title))

edited Feb 20 '14 at 12:31

answered Feb 20 '14 at 11:30

arocks

2,862
1
12
20

thanks for the answer. but will this not become a order_by(duedate, title) -- without priority_id ? – killua8p Feb 20 '14 at 12:05

How to sort a Django QuerySet by (field, custom function, field)

3 Answers3