[Solved]-Treat NULL as '0' in Django model

by

15👍

✅

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')
👤Jonny Buchanan

21👍

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))
👤Anton Belonovich

1👍

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

👤Maximiliano Villa

0👍

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)
👤Chandan Kumar Singh

Leave a comment