Django custom annotation function

Question

I want to build a simple hot questions list using Django. I have a function that evaluates "hotness" of each question based on some arguments.

Function looks similar to this (full function here)

def hot(ups, downs, date):
    # Do something here..
    return hotness

My models for question and vote models (relevant part)

class Question(models.Model):
    title = models.CharField(max_length=150)
    body = models.TextField()
    created_at = models.DateTimeField(auto_now_add=True)

class Vote(models.Model):
    question = models.ForeignKey(Question, related_name='questions_votes')
    delta = models.IntegerField(default=0)

Now, the delta attribute is either positive or negative. The hot function receives number of positive votes and number of negative votes and creation date of question.

I've tried something like this, but it isn't working.

 questions = Question.objects.annotate(hotness=hot(question_votes.filter(delta, > 0),question_votes.filter(delta < 0), 'created_at')).order_by('hotness')

The error I'm getting is: global name 'question_votes' is not defined
I understand the error, but I don't the correct way of doing this.

Answer 1

You can't use python functions for annotations. Annotation is a computation that is done on a database level. Django provides you only a set of basic computations which can be processed by the database - SUM, AVERAGE, MIN, MAX and so on... For more complex stuffs only from version 1.8 we have an API for more complex query expressions. Before Django 1.8 the only way to achieve similar functionality was to use .extra which means to write plain SQL.

So you basically have two options.

First

Write your hotness computation in plain SQL using .extra or via the new API if your Django version is >= 1.8.

Second

Create hotness field inside you model, which will be calculated by a cron job once a day (or more often depending on your needs). And use it for your needs (the hottest list).

Answer 2

For those looking for an updated answer (Django 2.0+) it is possible to subclass Func to generate custom functions for aggregations as per the documentation . There is a good explanation and example here about 80% of the way through the post in the "Extending with custom database functions" section.