django how to annotate to the count in select query values

Question

Using django2.0.2 python3.4 drf

skip details ex)meta,password,postcontent ...

models.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 is Order of reply on posts

replydepth is Order of replys on replys (like @username at facebook commtent)

myviews.py

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

re_replycount is like this query

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

i tried

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

this queryset in '???'

but return Strange result

Postreply table

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(),
    )))

if useruid = 1 want result

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

but i get

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

re_replycount is null

i want same value result of this 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 queryset

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')

and my 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

neverwalkaloner · Accepted Answer · 2018-05-21T10:07:30.107

1

You can use Case for conditional annotation. Try this:

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')

edited May 21 '18 at 10:07

answered May 21 '18 at 04:26

neverwalkaloner

46,181
7
92
100

i tried this code but returned always self.replydepth – Adam May 21 '18 at 07:27
@Adam also try to move `values` after `annotate`. – neverwalkaloner May 21 '18 at 08:27
um.... values move to after annotate but this is null this queryset.query print `SUM(CASE WHEN ('postreply'.'PostUID' = ('postreply'.'PostUID') AND 'postreply'.'ReplyRef' = ('postreply'.'ReplyRef') AND 'postreply'.'ReplyStep' > 0) THEN 1 ELSE NULL END) AS 're_replycount'` is this right? – Adam May 21 '18 at 09:57
@Adam yep, but you can specify default value as 0. See updates. – neverwalkaloner May 21 '18 at 10:06
sql in post run result re_replycount is 3 but django queryset always return default 0 ... – Adam May 22 '18 at 06:45
@Adam have no idea why it's not working. Does `print(queryset.query) return correct SQL query whick calculate re_replycount correctly? – neverwalkaloner May 22 '18 at 06:54
add sql and queryset.query in post – Adam May 22 '18 at 09:33
@Adam sorry I dont have any other ideas:( – neverwalkaloner May 28 '18 at 10:24
i think because of where UserUID AND ReplyDepth=0 can i re_replycount get from postreply.all() ? – Adam May 29 '18 at 02:11
@Adam sorry dont see how to do it. Try to unaccept this answer and wait for any other response. Probably somebody else can help you. – neverwalkaloner May 29 '18 at 03:55
problem is in Case(When()) is from same table as value so i tried `userinfomodel.postreply_set.filter(replydepth=0).select_related('postuid').annotate(postcontent=F("postuid_id__content"), re_replycount=Sum(Case( When(Q(postuid__postreply__replyref=F( "replystep")) & Q(postuid__postreply__replydepth__gt=0), then=1), output_field=IntegerField(), default=Value(0) )))` use postuid__replystep and postuid__replydepth worked – Adam May 31 '18 at 08:39

django how to annotate to the count in select query values

1 Answers1