Complex ordering in Django

Question

So I am pulling a list of links and I am attempting to sort these links by popularity. I am using the Hacker News algorithm:

Y Combinator's Hacker News:
Popularity = (p - 1) / (t + 2)^1.5

Votes divided by age factor.
Where

p : votes (points) from users.
t : time since submission in hours.

p is subtracted by 1 to negate submitter's vote.
Age factor is (time since submission in hours plus two) to the power of 1.5.factor is (time since submission in hours plus two) to the power of 1.5.

I accomplished this in MySQL and a PHP Framework by using an order by

(SUM(votes.karma_delta) - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5) DESC

Now I am using PostgreSQL and Django. I know this exact SQL probably won't work but I can do the converting later. The issue I am running into is I do not know how to get such a complex order_by in Django. My view has a perfect:

popular_links = Link.objects.select_related().annotate(karma_total = Sum('vote__karma_delta'))

And I don't really want to muck that up by using raw sql if I don't have to.

To summarize my question: how can I create a complex order_by in Django?

EDIT

There will be pagination and I really only want to sort the entries I pull. Is it just better to actually do that sorting in Python?

Answer 1

If you're going to pull the entire list anyway (that is, you aren't taking only the first 10 entries, for example), then you can do the sorting in Python.

Answer 2

No clean way but using extra() with your custom SQL:

popular_links = Link.objects.select_related().annotate(karma_total = Sum('vote__karma_delta'))
popular_links = popular_links.extra(
    select = {'popularity': '(karma_total - 1) / POW((TIMESTAMPDIFF(HOUR, links.created, NOW()) + 2), 1.5)',},
    order_by = ['-popularity',]
)