Django ORM: Ordering w/ aggregate functions — None special treatment

Question

Hi,

I'm doing query like that:

SomeObject.objects.annotate(something=Avg('something')).order_by(something).all()

Now, I normally have an aggregate field in my model that I use with Django signals to keep in sync, however in this case perfomance isn't an issue so I thought I'd keep it simple and just use subqueries.

This approach, however, presented an unexpected issue: It all works grate if aggregate function results are like this:

[5.0, 4.0, 6.0 … (etc, just numbers)]

However if you mix in some Nones than it's being ordered like this:

[None, 5.0, 4.0 …]

The issue is that None has higher value than any number, while it should have value at most of 0.

I'm using PostgreSQL and haven't tested w/ other DBs. I haven't actually checked what query is generated etc.

I worked it around by just sorting in memory:

sorted(…, key=lambda _:_.avg_rating if _.avg_rating is not None else 0) 

So I'm just curious if you know a way to do it w/ just Django ORM. Perhaps .where? or something…

Kind regards

Answer 1

How about just adding a has_something=1,0 via extra() and then order on both has_something and something?

with_avg = SomeObject.objects.annotate(avg=Avg('something'))
with_avg_and_has = with_avg.extra(select={'has_something': 'something is NULL'})
sorted_result = with_avg_and_has.order_by('-has_something', '-avg').all() 

Not 100% ORM in the strictest sense but it does push the sorting back into the DB.