Django - How does order_by work?

Question

I'd like to know how Django's order_by works if the given order_by field's values are same for a set of records. Consider I have a score field in DB and I'm filtering the queryset using order_by('score'). How will records having the same values for score arrange themselves?

Every time, they're ordered randomly within the subset of records having equal score and this breaks the pagination at client side. Is there a way to override this and return the records in a consistent order?

I'm Using Django 1.4 and PostgreSQL.

Solution 1

order_by can have multiple params, I think order_by('score', '-create_time') will always return the same queryset.

Solution 2

As the other answers correctly explain, order_by() accepts multiple arguments. I'd suggest using something like:

qs.order_by('score','pk') #where qs is your queryset

I recommend using 'pk' (or '-pk') as the last argument in these cases, since every model has a pk field and its value is never the same for 2 records.

Solution 3

If I understand correctly, I think you need consistently ordered result set every time, You can use something like order_by('score','id') that will first order by the score first and then by the auto-increment id within the score having same values, hence your output being consistent. The documentation is here. You need to be explicit in the order_by if you want to fetch correct result set every time, using 'id' is one of the ways.

Comments

[Hedde van der Heide]

-1 this answer was already given 2 times, no need for a third time, you could have edited their posts to clean up the coding parts

[Anuj Gupta]

Neither of the answers is completely accurate for the OP's needs. order_by('id', 'score') will sort by 'id' first, then by 'score', which is not what he needs. order_by('score', '-create_time') is not accurate since it uses an unknown column. Neither of the answers mentioned using pk which is universal.

[Hedde van der Heide]

point taken, but then again thats a minor comment on their posts and doesn't require a largely duplicate anwser