Django F() division - How to avoid rounding off

Question

I have this code:

q = MyModel.objects.order_by('-value1').annotate(
            res=ExpressionWrapper(
                (F('value1') / F('value2')), 
                output_field=FloatField()),
            )

for i in q:                                          
    print(i.value1, i.value2, i.res)

So, the output will be:

5 10 0.0
1 2 0.0

But I need

5 10 0.5
1 2 0.5

Wy F() rounded the result? How not to do this?

Thanks!

Answer 1

Unfortunately the ORM F('value1') / F('value2') operation is executed on the database side, therefore if both fields declared as integer you will definitely get the integer result. In Django 1.11.7 you could simply cast one of the F() expression to decimal like this:

 qs = (
     MyModel.objects
     .annotate(
         res=ExpressionWrapper(
             F('value1') * 1.0 / F('value2'), 
             output_field=FloatField(),
         ),
     )
 )

Answer 2

Simply make use of F()'s support for multiplication to convert one factor to decimal number.

Combined expression then would look like:

from decimal import Decimal

q = MyModel.objects.order_by('-value1').annotate(
            res=ExpressionWrapper(
                (F('value1') * Decimal('1.0') / F('value2')), 
                output_field=FloatField()),
            )

I find this more elegant way then write raw SQL CAST on value1 field and then do the division.

Answer 3

The result you are expecting is really easy to achieve with a raw query and really, I mean really hard to achieve with pure django.

from django.db.models import FloatField, ExpressionWrapper, F

template = '%(function)s(%(expressions)s AS FLOAT)'
fv1 =  Func(F('value1'), function='CAST', template=template)
fv2 =  Func(F('value2'), function='CAST', template=template)
ew = ExpressionWrapper(fv1/fv2, output_field = FloatField())


q = MyModel.objects.order_by('-value1').annotate(res = ew)

You wouldn't accuse this of being elegant but it works on both Mysql and Postgresql.

To provide some background. The rounding is done by the database doing integer division because the field you have are ints. If you want decimal division you need to cast them to decimals. Unfortunately casting is not very easy with Django.

Postgresql has a really elegant way to cast to float. value1::float but this cannot be made use of from inside django (at least as far as I know)

Answer 4

In v1.10, Django introduced a Cast function which makes this (almost) a breeze – the qualification being that you either need two casts or need to wrap the whole thing in an ExpressionWrapper to avoid a FieldError: Expression contains mixed types. You must set output_field.

q = MyModel.objects.annotate(
            res=ExpressionWrapper(
                (Cast('value1', FloatField()) / F('value2')), 
                output_field=FloatField()),
            )

This is functionally equivalent to multiplying the enumerator by 1.0, but an explicit cast makes your intention clearer than a multiplication by a number.