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

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 2

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)