Sort a python Django query with zero always first, then positives, then negatives

Question

I have Django objects set up like:

class Example(models.Model):
    count = models.CharField(default="0")

So if I have some objects ex1, ex2, ex3, ex4 that have count of -1, 0, 5, -6 respectively. I want to be able to query the objects and sort them into the order [0, 5, -6, -1] where any zeros come first, then positives, then negatives while also going in ascending order for each section. I was thinking about using something like Example.objects.order_by('count') but did not find a way to do that with a custom function like this.

The other route I was looking at was something like below:

objs = Example.objects.all()
sorted_objs = sorted(objs, key = lambda o: int(o.count))

Is there a way to use the sorted method to sort the zeros first? I was unable to find one.

The final way I am using is:

objs = Example.objects.all()
zero_objs = []
positive_objs = []
negative_objs = []
for obj in objs:
    if obj.count == 0:
        zero_objs.append(obj)
    elif obj.count < 0:
        negative_objs.append(obj)
    else:
        postitive_objs.append(obj)
sorted_objs = zero_objs + sorted(postitive_objs) + sorted(negative_objs)

This works but seems like it's not the best way to do this, so any ideas would be great. As a side note, I am aware the count attribute would be better stored as an integer field, but I would like to accomplish this while keeping it a char field.

Are you looking to do this at the DB level so your query returns the results with that criteria or at the Python level? — Jon Clements, Dec 03 '18 at 22:25
Why is count a CharField? That really complicates things unnecessarily. Make it an IntegerField. — manassehkatz-Moving 2 Codidact, Dec 03 '18 at 22:25
'cos something like: `sorted_objs = sorted(Example.objects.all(), lambda obj: (obj.count != 0, obj.count < 0, obj.count))` will do it in Python for you - might not be efficient though depending how big your total objects are though... and won't work on charfields properly as @manassehkatz points out... — Jon Clements, Dec 03 '18 at 22:30

score 4 · Answer 1 · edited Jan 24 '20 at 16:57

You can use case to add a new field for ordering purposes:

from django.db.models import Case, IntegerField, Value, When
Example.objects.annotate(
    o=Case(
        When(count=0, then=Value(0)),
        When(count__gt=0, then=Value(1)),
        When(count__lt=0, then=Value(2)),
        output_field=IntegerField(),
    ),
).order_by('o', 'count')

Explanation:

Order clause will be composed by both: the "new field", count. With "new field" you raise set of rows with count = 0 to top, then set of positive numbers at last set of negatives. The second field, count, do the ascending sort for each set.

Disclaimer:

Be aware, this is not an index friendly solution! If you need performance, then, just create this new field on model and figure out it before save.

Sort a python Django query with zero always first, then positives, then negatives

1 Answers1

Linked