[Answer]-Django model queryset usage of .extra() or union to apply a custom sort on a PositiveIntegerField

by

1👍

✅

Doing an union/merge of querysets is not the solution. A much more simple solutions is to use a comparison in the order statement. An answer on stackeoverflow pointed me to this neat solution.

select * from photos order by sort=0, sort

0=0 is evaluated as True which equal 1, 1=0 is False which equal 0 etc. got it?

Translated into Django Queryset :

Photos.objects.extra(select={'sortiszero': 'sort = 0'}, order_by=['sortiszero', 'sort'])

Results:

+---------+------+
| photo   | sort |
+---------+------+
| photo-a | 1    |
| photo-b | 2    |
| photo-c | 0    |
| photo-d | 0    |
| photo-e | 0    |
| ...            |

Voilà!

👤nrako

0👍

You should give more details.

If you want to union this two sets you can simply add lists:

list1 = SomeModel.objects.filter(sort != 0).order_by('column_name_1','column_name_2')
list2 = SomeModel.objects.filter(sort = 0).order_by('priority','foo')
return list = list1 + list2

Or if you want to use extra() method, here is an exaple:

list = SomeModel.objects.fooMethod().extra(order_by=[foo,])
👤arturex

Leave a comment