Treat NULL as '0' in Django model

Question

I use the following bit of code in my Django app:

pictures = gallery.picture_set.annotate( score=models.Sum( 'picturevote__value' ) ).order_by( '-score' )

There is a table of galleries. In each of them are some pictures. When a user votes up or down a picture, a new row in 'picturevote' is inserted and connected to the picture. Then I can get the total score for the pictures. Now I want to order the pictures of one gallery by their score values. But due to the table joins there can be the value NULL for score when there were no votes at all. Nevertheless a score of 'NULL' shall be treated as '0'.

Any ideas?

edit: Okay, here some additional explanation: The problem is that the aggregation in the above example sets score to NULL. When I want to display the score I use something like this:

score = self.picturevote_set.aggregate( models.Sum( 'value' ) )[ 'value__sum' ] or 0

Then the aggregation leads either to NULL (if there are no picturevote rows) or a certain value. If it is NULL the or-expression converts it to a displayable integer value. But this solves just the display problems which are caused by the NULL value. When I want to sort the pictures by this score value as in the first code example all entries with NULL are put at the end of the ordered result set. First there are pictures with positive scores, then there are the pictures with negative values and THEN there are the pictures that were not voted up or down so far, because they have NULL as score.

My question is how this behaviour can be changed so that the order is correct.

Daugavpils

Can you state more clearly what the problem is? Do you get an error from the above code if there are no votes? What's the error? – Uitlander

Answer 1

Prior to the introduction of annotations, you might have used extra to do something like this, which I think should return 0 in cases where there are no votes (if it doesn't for any particular database implementation, you can at least directly insert the necessary COALESCE function call - COALESCE(SUM(value), 0) - using this method):

pictures = gallery.picture_set.extra(
    select={
        'score': 'SELECT SUM(value) FROM yourapp_picturevote WHERE yourapp_picturevote.picture_id = yourapp_picture.id',
    },
    order_by=['-score']
)

I can't see any built-in way to add your own SQL to the new annotation stuff (which I haven't personally used yet), but it looks like you should be able to create a new annotation like so:

from django.db.models import aggregates
from django.db.models.sql import aggregates as sql_aggregates

class SumWithDefault(aggregates.Aggregate):
    name = 'SumWithDefault'

class SQLSumWithDefault(sql_aggregates.Sum):
    sql_template = 'COALESCE(%(function)s(%(field)s), %(default)s)'

setattr(sql_aggregates, 'SumWithDefault', SQLSumWithDefault)

This looks rather ugly as you need to monkeypatch the new aggregate into django.db.models.sql.aggregates due to the way the SQL aggregate classes are looked up, but all we've done here is added a new aggregate which subclasses Sum, hardcoding a call to the COALESCE function and adding a placeholder for the default value, which you must supply as a keyword argument (in this very basic example implementation, at least).

This should let you do the following:

pictures = gallery.picture_set.annotate(score=SumWithDefault('picturevote__value', default=0).order_by('-score')

Lepto

Hello. This is after a very long time, however I think that there is an error in your sql_template. You have to write it like this: sql_template = '%(function)s(COALESCE(%(field)s, %(default)s))' . Please think about it and tell me if you agree - this has been haunting me for months ! – Coinage

django1.8 now supports Coalesce as a database function – Homograph

Answer 2

From Django 1.8, there is a Coalesce database function. Your query might look like this:

from django.db.models.functions import Coalesce    

score = self.picturevote_set.aggregate(Coalesce(models.Sum('value'), 0))

Jessalyn

There is also open feature request, and comment of Django core developer: code.djangoproject.com/ticket/10929#comment:16 – Jessalyn

since the "Feature request" is around since 12 years ... I think Coalesce is the best workaround – Tracery

Answer 3

According to this issue now you can do the following :

score = self.picturevote_set.aggregate((models.Sum('value', default=0))

If the result of sum is None type, it returns the dafault value. In this case number 0.

Note: it was released in Django V4

Settling

Answer 4

You can treat with Case, when like sql using below code.

from django.db.models import Value, Case, When
zero_value = Value(0, output_field=IntegerField())
YourModel.objects.filter(**kwargs)
.annotate(rename_your_column=Case(When(your_col==None), 
 default=zero_value)

Aecium