162

what I'm trying to do is this:

  • get the 30 Authors with highest score ( Author.objects.order_by('-score')[:30] )

  • order the authors by last_name

Any suggestions?

Ned Batchelder
  • 364,293
  • 75
  • 561
  • 662
RadiantHex
  • 24,907
  • 47
  • 148
  • 244
  • 4
    @RH: so how about checking AlexMartelli's answer as the correct solution? (not that he needs any more rep, unless he's going after that Skeet guy...) – PaulMcG Mar 10 '10 at 01:42

3 Answers3

261

What about

import operator

auths = Author.objects.order_by('-score')[:30]
ordered = sorted(auths, key=operator.attrgetter('last_name'))

In Django 1.4 and newer you can order by providing multiple fields.
Reference: https://docs.djangoproject.com/en/dev/ref/models/querysets/#order-by

order_by(*fields)

By default, results returned by a QuerySet are ordered by the ordering tuple given by the ordering option in the model’s Meta. You can override this on a per-QuerySet basis by using the order_by method.

Example:

ordered_authors = Author.objects.order_by('-score', 'last_name')[:30]

The result above will be ordered by score descending, then by last_name ascending. The negative sign in front of "-score" indicates descending order. Ascending order is implied.

Martin
  • 2,135
  • 8
  • 39
  • 42
Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • 1
    @Alex: grazie alex! That was great, I'm off to read about the operator module! – RadiantHex Mar 09 '10 at 21:52
  • 3
    Is this more efficient than Author.objects.order_by('-score', 'last_name')[:30]? – Brian Luft Mar 10 '10 at 00:45
  • 4
    @Brian - if by "more efficient" you mean "more correct" then yes, its. :) Your solution keeps the authors pretty much sorted by score, and only alphabetizes those authors with the same score (which is how secondary keys work). Alex shows how to take the results, and then apply a completely different sort order to them (using key=operator.attrgetter to define a per-object key expression), which is what the OP asked for. – PaulMcG Mar 10 '10 at 01:39
  • @Paul: that's not what I asked though, Alex's answer is the correct one! – RadiantHex Mar 11 '10 at 15:42
  • @RH: Sorry for my obtuseness of expression - I was trying to say the same thing, that Alex's posted solution, as usual, is the correct one. @jathanism nicely walks through why using multiple args to order_by gives a result that does not match your original request. – PaulMcG Mar 11 '10 at 17:06
  • From my understanding, order_by(x, y, z) sorts result by x first, then takes the result and sorts the items with the same x by y, then takes that result and sorts the items with the same y by z. Sort is done by the DB and Django returns a lazy result. Doing it in Python will(?) fetch 'last_name' from the db for all items before doing the sort. Paul, I see where you are going with this, as I am having hard time NOT translating the question to one that can be solved by order_by. – Val Neekman Apr 02 '13 at 21:01
  • 4
    Why not making the sorting key function `lambda x: x.last_name`? It's shorter, more verbose and doesn't need any import. – Krzysztof Szularz Dec 13 '13 at 12:16
  • Just what I was looking for – Robert Johnstone Mar 21 '14 at 13:44
  • What is [:30] at the end? – E.Praneeth Nov 27 '18 at 17:32
  • @E.Praneeth the [:30] is limiting the results to the 30 objects in the query, in this case the 30 highest scores. – Carl Brubaker Dec 04 '18 at 13:31
  • It's a good solution. But as it comes to sort the qs by object's foreign key's field. The lambda function can't work on it. :;( – Alston Mar 15 '23 at 09:54
17

I just wanted to illustrate that the built-in solutions (SQL-only) are not always the best ones. At first I thought that because Django's QuerySet.objects.order_by method accepts multiple arguments, you could easily chain them:

ordered_authors = Author.objects.order_by('-score', 'last_name')[:30]

But, it does not work as you would expect. Case in point, first is a list of presidents sorted by score (selecting top 5 for easier reading):

>>> auths = Author.objects.order_by('-score')[:5]
>>> for x in auths: print x
... 
James Monroe (487)
Ulysses Simpson (474)
Harry Truman (471)
Benjamin Harrison (467)
Gerald Rudolph (464)

Using Alex Martelli's solution which accurately provides the top 5 people sorted by last_name:

>>> for x in sorted(auths, key=operator.attrgetter('last_name')): print x
... 
Benjamin Harrison (467)
James Monroe (487)
Gerald Rudolph (464)
Ulysses Simpson (474)
Harry Truman (471)

And now the combined order_by call:

>>> myauths = Author.objects.order_by('-score', 'last_name')[:5]
>>> for x in myauths: print x
... 
James Monroe (487)
Ulysses Simpson (474)
Harry Truman (471)
Benjamin Harrison (467)
Gerald Rudolph (464)

As you can see it is the same result as the first one, meaning it doesn't work as you would expect.

jathanism
  • 33,067
  • 9
  • 68
  • 86
  • 14
    Your result #3 is sorting descending by score and then by last_name IFF any objects have the same score. The problem is none of the objects in your result set have the same score so only the '-score' is affecting the sort order. Try setting 3 authors score to 487 and run #3 again. – istruble Mar 10 '10 at 01:52
  • Yeah I understand that. I really just wanted to illustrate that the built-in solutions (SQL-only) are not always the best ones. – jathanism Mar 10 '10 at 14:24
  • 3
    It did exactly what I expected: lexicographic ordering (which is kinda' trivial if the first sorting key is all distinct). – Jonas Kölker Sep 09 '12 at 08:16
6

Here's a way that allows for ties for the cut-off score.

author_count = Author.objects.count()
cut_off_score = Author.objects.order_by('-score').values_list('score')[min(30, author_count)]
top_authors = Author.objects.filter(score__gte=cut_off_score).order_by('last_name')

You may get more than 30 authors in top_authors this way and the min(30,author_count) is there incase you have fewer than 30 authors.

istruble
  • 13,363
  • 2
  • 47
  • 52