时间戳

首页 > 解决方案 > django如何注释选择查询值中的计数

django - django如何注释选择查询值中的计数

问题描述

使用django2.0.2 python3.4 drf

跳过细节前）元，密码，postcontent ...

模型.py

class Userinfo(models.Model)
    usersuid = BigAutoField(db_column='UserUID', primary_key=True)
    username = models.CharField(db_column='UserName')

class Postinfo(models.Model)
    postuid = BigAutoField(db_column='PostUID', primary_key=True)
    useruid = models.ForeignKey(
    'Userinfo', db_column='UserUID', on_delete=models.CASCADE)

class Postreply(models.Model)
    replyuid = BigAutoField(db_column='ReplyUID', primary_key=True)
    useruid = models.ForeignKey(
    'Userinfo', db_column='UserUID', on_delete=models.CASCADE)
    postuid = models.ForeignKey(
    'Postinfo', db_column='PostUID', on_delete=models.CASCADE)
    replystep = models.IntegerField(db_column='ReplyStep')
    replydepth = models.IntegerField(db_column='ReplyDepth')

replystep 是帖子的回复顺序

replydepth 是回复的回复顺序（如 facebook commtent 中的@username）

我的观点.py

usermodel = Userinfo.objects.get(useruid=request.get("useruid"))
usermodel.postreply_set.all().values('replyuid','postuid','useruid','replystep','replydeth').annotate(re_replycount=('???'))

re_replycount 就像这个查询

SELECT COUNT(*) FROM PostReply WHERE PostUID = t1.PostUID AND ReplyStep = t1.ReplyStep AND ReplyDepth > 0

我试过

Postreply.objects.filter((Q(postuid=F('postuid')) & Q(replystep=F("replystep")) & Q(replydepth__gt=0))).count(), output_field=IntegerField())

这个查询集在'???'

但返回奇怪的结果

回复后表

replyuid | useruid | postuid | replystep | replydepth

    1         1         1          0            0
    2         1         1          1            0
    3         2         1          0            1
    4         1         1          0            2

usermodel.postreply_set.filter(replydepth=0).values('replyuid','postuid','useruid','replystep','replydeth').annotate(re_replycount=Sum(Case(
        When(Q(postuid=F('postuid')) & Q(replystep=F("replystep")) & Q(replydepth__gt=0), then=1),
        output_field=IntegerField(),
    )))

如果 useruid = 1 想要结果

{replyuid :1 ,useruid:1,postuid:1,replystep:0,replydepth:0,re_replycount:2}

但我明白了

  {replyuid :1 ,useruid:1,postuid:1,replystep:0,replydepth:0,re_replycount:null}

re_replycount 为空

我想要这个 sql 的相同值结果

  SELECT 0 AS ReplyUIDSort, t1.ReplyUID, t1.PostUID, t1.UserUID, t1.Content,
       t1.ReplyStep, t1.ReplyDepth, t1.RegisterDate, t2.Content AS PostContent, (SELECT COUNT(*) FROM PostReply WHERE PostUID = t1.PostUID AND ReplyStep = t1.ReplyStep AND ReplyDepth > 0) AS Re_ReplyCount
FROM   WorryReply t1 
       INNER JOIN WorryInfo t2 
       ON         t1.WorryUID = t2.WorryUID 
WHERE  t1.UserUID = i_UserUID
AND    t1.ReplyDepth = 0

Django 查询集

userinfomodel.postreply_set.filter(replydepth=0).select_related('postuid').annotate(postcontent=F("postuid_id__content"), re_replycount=Sum(Case(
            When(Q(postuid=F('postuid')) & Q(replyref=F(
                "replystep")) & Q(replydepth__gt=0), then=1),
            output_field=IntegerField(),
            default=Value(0)
        ))).values('ReplyUID','PostUID','UserUID','Content', 'ReplyStep','ReplyDepth', 'RegisterDate','postcontent','re_replycount')

和我的 queryset.query

    SELECT 'postreply'.'ReplyUID', 'postreply'.'PostUID', 'postreply'.'UserUID',
  'postreply'.'Content', 'postreply'.'ReplyStep', 'postreply'.'ReplyDepth', 'postreply'.'RegisterDate',  'postinfo'.'Content' AS 'postcontent',  SUM(CASE WHEN ('postreply'.'PostUID' = ('postreply'.'PostUI
D') AND 'postreply'.'ReplyStep' = ('postreply'.'ReplyStep') AND 'postreply'.'Re
plyDepth' > 0) THEN 1 ELSE 0 END) AS 're_replycount' FROM 'postreply' INNER JOIN 's
toryinfo' ON ('postreply'.'PostUID' = 'postinfo'.'PostUID') WHERE ('postrep
ly'.'UserUID' = 243 AND 'postreply'.'ReplyDepth' = 0) GROUP BY 'postreply'.'Rep
lyUID', 'postinfo'.'Content' ORDER BY NULL

标签： djangodjango-modelsdjango-queryset

解决方案

您可以Case用于条件注释。试试这个：

from django.db.models import Sum, Case, When, IntegerField, Value

usermodel.postreply_set.all().annotate(re_replycount=Sum(Case(
        When(Q(postuid=F('postuid')) & Q(replystep=F("replystep")) & Q(replydepth__gt=0), then=1),
        output_field=IntegerField(),
        default=Value(0)   
    ))).values('replyuid','postuid','useruid','replystep','replydeth', 're_replycount')

推荐阅读