在Django模型中将NULL视为'0' [英] Treat NULL as '0' in Django model
问题描述
我在Django应用程式中使用下列位元码：
I use the following bit of code in my Django app:
pictures = gallery.picture_set.annotate( score=models.Sum( 'picturevote__value' ) ).order_by( '-score' )
画廊。在每一个都是一些图片。当用户向上或向下投票图片时，picturevote中的新行被插入并连接到图片。然后我可以得到图片的总分。
现在我想按照他们的得分值来订购一个画廊的图片。但是由于表连接，当没有投票时，可以将值设置为NULL。
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'.
任何想法？
编辑：
好吧，这里有一些额外的解释：
问题是上面例子中的聚合将 score 设置为NULL。当我想显示分数时，我使用这样的：
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
然后聚合导致NULL不是picturevote行）或某个值。如果为NULL，or-expression将其转换为可显示的整数值。
但是这只解决了由NULL值引起的显示问题。
当我要按照 score 值对照片进行排序时，如第一个代码示例中所有具有NULL的条目都放在有序结果集的末尾。首先是具有正分数的图片，然后是具有负值的图片，并且因此它们具有NULL作为 score 。
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.
推荐答案
在引入注释之前，您可能使用了 extra 在没有投票的情况下（如果它不适用于任何特定的数据库实现，我会应该返回 0 你至少可以直接插入必要的 COALESCE 函数调用 - COALESCE（SUM（value），0）方法）：
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']
)
我看不到任何内置的方式来添加自己的SQL到新的注释的东西（我没有亲自使用） ，但它看起来像你应该能够创建一个新的注释，如：
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)
这看起来相当难看，因为你需要将新的聚合修改为 django.db.models.sql.aggregates ，因为SQL聚合类被查找，但是我们在这里所做的是添加了一个新的聚合，子类 Sum ，对 COALESCE 函数进行硬编码，并为默认值添加占位符，必须
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')
这篇关于在Django模型中将NULL视为'0'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
