Django annotate with casted variable

Question

i want to achieve something complex with a django queryset. My model looks like this:

class Mymodel(models.Model):
    #Model Variables
    year = models.IntegerField()
    month = models.IntegerField()
    date = models.DateField(_("Date"), default=datetime.date.today)
    score = models.CharField(max_length=50)

Unfortunately,

score' can't change from CharField() to FloatField()

, it should be kept as a String.

What i need is to sum all scores by year and month. I tried something like this to group all scores with no great success.

values = Mymodel.objects.all().values(
    'year', 'month').annotate(Sum('score')).order_by('year', 'month')

I tried to cast 'score' before use annotate by doing this.

values = Mymodel.objects.all().annotate(
        scoreFloat=Cast('score', FloatField())
    ).values('year', 'month').annotate(
        Sum('scoreFloat')).order_by('year', 'month')

Once again with no success, since i am getting a KeyError for scoreFloat parameter.

Any suggestions?

Why do you use `charfield` anyway? If something is numerical, store it in a numerical type object. It is horrible modeling to use `CharField`s for that. — Willem Van Onsem, Jun 25 '18 at 20:03
You are right, but i can't change it now, as i'll probably break a lot of working features. — Paris Karipidis, Jun 26 '18 at 07:49

Brown Bear · Accepted Answer · 2018-06-26T08:15:26.293

0

In your second query you should add to the values new field from first annotate:

Mymodel.objects.annotate(as_float=Cast('score', FloatField())
    ).values('year', 'month', 'as_float'
    ).order_by('year', 'month'
    ).annotate(sumscore=Sum('as_float')).values('year', 'month', 'sumscore')

edited Jun 26 '18 at 08:15

answered Jun 25 '18 at 20:11

Brown Bear

19,655
10
58
76

This code returns my several objects with the same month, for example a) 'year': 2018, 'sumscore': 5, 'month': 4 b) 'year': 2018, 'sumscore': 3, 'month': 5 c) 'year': 2018, 'sumscore': 7, 'month': 5 as long b and c belongs to the same month i want somehow to be like this: a) 'year': 2018, 'sumscore': 5, 'month': 4 b) 'year': 2018, 'sumscore': 10, 'month': 5 (previous b+c) – Paris Karipidis Jun 26 '18 at 07:47
Unfortunately the casting is not working, i am getting exact the same output as previous. If i change score from char to float even without casting (obviously) the queryset works properly. So that leaves me no option, score changed to float. – Paris Karipidis Jun 26 '18 at 09:49
strange, i generate test data and all works fine, please add code to generate data like yours inside the question – Brown Bear Jun 26 '18 at 11:49
Yeap you're right, my fault. Just forgot to change the name in the first annotation. It works now. – Paris Karipidis Jun 26 '18 at 13:06
glad to help you! – Brown Bear Jun 26 '18 at 13:13

Django annotate with casted variable

1 Answers1