Geek Answers Handbook

Treat NULL as "0" in a Django Model

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

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

There is a table of galleries. Each of them has several photos. When the user votes up or down the picture, a new line in "picturevote" is inserted and connected to the picture. Then I can get a total score for photos. Now I want to order photos from one gallery by their values. But due to table joins, there may be a NULL value for evaluation when there were no votes at all. However, a "NULL" rating should be considered a "0".

Any ideas?

edit: Well, here are some additional explanations: The problem is that the aggregation in the above example sets scoreto NULL. When I want to display a grade, I use something like this:

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

Aggregation then results in either NULL (if there are no image lines) or a specific value. If it is NULL, the or expression converts it to a displayed integer value. But this only solves the display problems caused by the NULL value. When I want to sort pictures using this value score, as in the first code example, all entries with NULL are put at the end of the ordered result set. First there are photos with positive ratings, then there are images with negative values, and THEN there are images that were not voted up or down because they are NULL like score.

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

+5
2

extra, - , , , 0 , ( - , COALESCE - COALESCE(SUM(value), 0) - ):

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

SQL ( ), , , :

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)

, django.db.models.sql.aggregates - , SQL, , , , Sum, hardcoding COALESCE , ( , ).

:

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

Django 1.8 Coalesce. :

from django.db.models.functions import Coalesce    

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

More articles:


All Articles