注释SUM聚合函数导致Django中的“无”值 [英] Annotating SUM aggregation function leading to 'None' value in Django

问题描述

做我的第一个真实的Django项目，需要指导。

Doing my first real Django project, and need guidance.

背景：
我的项目是Reddit克隆。用户提交链接+文本。访客赞成或反对投票。有一个social_ranking算法，每隔约2分钟运行一次作为背景脚本，根据净投票和内容的新鲜度对所有提交的内容进行排名。

Background: My project is a reddit clone. Users submit links+text. Visitors upvote or downvote. There's a social_ranking algo, runs every ~2 mins as a background script, reranks all the submissions according to net votes and freshness of content. Fairly vanilla stuff.

问题：
按投票排序是'无法正常工作，因为投票被初始化为 None 而不是 0 。这将导致以无投票的提交排名低于具有否定投票的提交。我已经调试了好几天了-没运气。

Problem: Ordering by votes isn't working correctly, because votes are being initialized as None instead of 0. This causes submissions with None votes to rank below submissions with negative votes. I've debugged this issue for days - no luck.

详细信息：
我已经超越了模型的模型管理器将 Sum 聚合函数注释到查询集，然后按社交等级和投票对所述查询集进行排序。

Specifics: I've over-ridden my model's model manager to annotate a Sum aggregation function to the query set, and then order the said query set by 'social rank' and votes.

下面是我的 models.py 。我正在使用 Django 1.5 ，因此您在此处看到的某些内容可能与1.8不对应（例如 get_query_set vs get_queryset ）：

Below is my models.py. I'm using Django 1.5, thus some stuff you see here may not correspond to 1.8 (e.g. get_query_set vs get_queryset):

class LinkVoteCountManager(models.Manager):
    def get_query_set(self):
        return super(LinkVoteCountManager, self).get_query_set().annotate(votes=Sum('vote__value')).order_by('-rank_score', '-votes') 

class Link(models.Model):
    description = models.TextField(_("Write something"))
    submitter = models.ForeignKey(User)
    submitted_on = models.DateTimeField(auto_now_add=True)
    rank_score = models.FloatField(default=0.0)
    url = models.URLField(_("Link"), max_length=250, blank=True)

    with_votes = LinkVoteCountManager() 
    objects = models.Manager() 

    def __unicode__(self): 
        return self.description

    def set_rank(self):
        # Based on reddit ranking algo at http://amix.dk/blog/post/19588
        epoch = datetime(1970, 1, 1).replace(tzinfo=None)
        netvotes = self.votes # 'NONE' votes are messing up netvotes amount.
        if netvotes == None:
            netvotes = 0
        order = log(max(abs(netvotes), 1), 10)
        sign = 1 if netvotes > 0 else -1 if netvotes < 0 else 0
        unaware_submission = self.submitted_on.replace(tzinfo=None)
        td = unaware_submission - epoch 
        epoch_submission = td.days * 86400 + td.seconds + (float(td.microseconds) / 1000000)
        secs = epoch_submission - 1432201843
        self.rank_score = round(sign * order + secs / 45000, 8)
        self.save()

class Vote(models.Model):
    voter = models.ForeignKey(User)
    link = models.ForeignKey(Link)
    value = models.IntegerField(null=True, blank=True, default=0)

    def __unicode__(self):
        return "%s gave %s to %s" % (self.voter.username, self.value, self.link.description)

如果需要，以下是我 views.py 中的相关部分：

If needed, the following are relevant sections from my views.py:

class LinkListView(ListView):
    model = Link
    queryset = Link.with_votes.all()
    paginate_by = 10

    def get_context_data(self, **kwargs):
        context = super(LinkListView, self).get_context_data(**kwargs)
        if self.request.user.is_authenticated():
            voted = Vote.objects.filter(voter=self.request.user)
            links_in_page = [link.id for link in context["object_list"]]
            voted = voted.filter(link_id__in=links_in_page)
            voted = voted.values_list('link_id', flat=True)
            context["voted"] = voted
        return context

class LinkCreateView(CreateView):
    model = Link
    form_class = LinkForm

    def form_valid(self, form):
        f = form.save(commit=False)
        f.rank_score=0
        f.with_votes = 0
        f.category = '1'
        f.save()
        return super(CreateView, self).form_valid(form)

有人可以阐明我为解决 无问题需要采取的措施吗？提前致谢。

Can anyone shed light on what I need to do to fix the "None" issue? Thanks in advance.

推荐答案

虽然我选择忽略 None 排除结果中的条目。

Just hit the same wall, although I chose to ignore None entries by excluding them out of the results. Guess you don't want that.

顺便说一句，这个问题与对总和进行注释会导致None而不是零

BTW, this question has the same issue Annotating a Sum results in None rather than zero

至于解决方案其他而不是使用在该问题的答案中指出的自定义sql，您可以改用Django 1.8，并使用在Django的错误跟踪器中打开超过6年的票证中指出的解决方案（！） https://code.djangoproject.com/ticket/10929

As for the solution other than using a custom sql as pointed out in that question's answer, you can use Django 1.8 instead and go for the solution pointed out in the ticket open in Django's bug tracker for over 6 years(!) https://code.djangoproject.com/ticket/10929

Coalesce(Sum('field'), 0)

所以您的经理将是：

class LinkVoteCountManager(models.Manager):
    def get_query_set(self):
        return super(LinkVoteCountManager, self).get_query_set().annotate(
            votes=Coalesce(Sum('vote__value'), 0)
        ).order_by(
            '-rank_score', 
            '-votes'
        )

PS：我还没有测试了代码，因为我自己没有使用Django 1.8。

PS: I have not tested the code, since I'm not using Django 1.8 myself.

这篇关于注释SUM聚合函数导致Django中的“无”值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！