SOLVED: Django ORM: How to round down (truncate) a query number?

Question

SOLVED

I'm working with sensitive currency values. In my case, i have to reproduce a sheet with it's formulas. The point is that i need to round down a currency value with 2 decimal places. A practical example is the number: 9809.4069, it should be rounded to 9809.40, with a truncation. Otherwise, normal rounding function returns me 9809.41.

Obs. For performance reasons i need to bring all my values in one query. Usual ways, with normal functions as round(), doesen't work inside query functions.

Well, my Query i'ts working completly FINE and brings everything that i want, the problem are the fields with Round() function, that returns me the "wrong" value.

The solution: (I'm using MySQL) was to create my own function that implements Trunc native function like the code below:

class Truncate(Func):
    function = 'Truncate'
    output_field=FloatField()

# In the annotate:
Truncate(F('item_value') * F('availability_percentage'),2)

The Query:

activetime_qs = activetime_qs.values(
            'user', 'user__team__name','user__foreign_id','user__location','user__team__cost_center'
            ).annotate(
                full_name=Concat(('user__first_name'),Value(' '),('user__last_name')),
                project_id=Subquery(UserProject.objects.filter(user_id=OuterRef('user')).values('project')[:1]),
                item_id=Subquery(Project.objects.filter(id=OuterRef('project_id')).values('item')[:1],),
                project_name=Subquery(Project.objects.filter(id=OuterRef('project_id')).values('name')[:1]),
                project_code=Subquery(Project.objects.filter(id=OuterRef('project_id')).values('code')[:1]),
                item_name=Subquery(Item.objects.filter(id=OuterRef('item_id')).values('name')[:1]),
                item_value=Subquery(Item.objects.filter(id=OuterRef('item_id')).values('unitary_value')[:1]),
                available_time=Sum('duration'),
                completed_tasks_amount=Case(
                    When(user__activity_type=ActivityType.DELIVERABLE,
                        then=Subquery(TaskExecution.objects.filter(members=OuterRef('user'), completed=True, 
                                start__date__gte=initial_date, start__date__lte=final_date)
                            .values('pk').annotate(count=Func(F('pk'), function='Count'))
                            .values('count'), output_field=IntegerField()
                            )
                    ),
                    default=1,
                ),
                availability_percentage=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION, 
                        then=Round(F('available_time') / expected_time, 3)),
                    default=0,
                    output_field=FloatField()
                ),
                subtotal_value=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION,
                        then=Round(F('item_value') * F('availability_percentage'),2)
                    ),
                    default=Round(F('item_value') * F('completed_tasks_amount'),3),
                    output_field=FloatField()
                ),
                availability_discount=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION,
                        then=Round(-1 + F('availability_percentage'),3),
                    ),
                    default=0,
                    output_field=FloatField()
                ),
                discount_value=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION, 
                        then=Round(F('item_value') - F('subtotal_value'),2),
                    ),
                    default=0,
                    output_field=FloatField()
                ),
            )
        return activetime_qs

Then, i've tried two aprocches to round the annotate values down.

1. Use a DecimalField as output_field with the exact number of decimal places that i need and set the decimal round method to ROUND_DOWN.

*My custom DecimalFieldClass:

class RoundedDecimalField(FloatField):

    def __init__(self, *args, **kwargs):
        super(RoundedDecimalField, self).__init__(*args, **kwargs)
        self.context = decimal.Context(prec=self.max_digits, rounding=decimal.ROUND_DOWN)

    def context(self):
        return decimal.Context(prec=self.max_digits, rounding=decimal.ROUND_DOWN)

    def to_python(self, value):
        self.context = decimal.Context(prec=self.max_digits, rounding=decimal.ROUND_DOWN)
        if value is None:
            return value
        if isinstance(value, float):
            if math.isnan(value):
                raise exceptions.ValidationError(
                    self.error_messages["invalid"],
                    code="invalid",
                    params={"value": value},
                )
            return self.context.create_decimal_from_float(value)
        try:
            return self.context.create_decimal(value)
        except (decimal.InvalidOperation, TypeError, ValueError):
            raise exceptions.ValidationError(
                self.error_messages["invalid"],
                code="invalid",
                params={"value": value},
            )

The annotate values with this approach:

                completed_tasks_amount=Case(
                    When(user__activity_type=ActivityType.DELIVERABLE,
                        then=Subquery(TaskExecution.objects.filter(members=OuterRef('user'), completed=True, 
                                start__date__gte=initial_date, start__date__lte=final_date)
                            .values('pk').annotate(count=Func(F('pk'), function='Count'))
                            .values('count'), output_field=IntegerField()
                            )
                    ),
                    default=1,
                ),
                availability_percentage=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION, then=Cast(Round(F('available_time') / expected_time, 3), output_field=RoundedDecimalField(max_digits=4,decimal_places=3))),
                    default=Cast(0, output_field=RoundedDecimalField(max_digits=4,decimal_places=3)),
                    output_field=RoundedDecimalField(max_digits=4,decimal_places=3)
                ),
                subtotal_value=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION,
                        then=Cast(F('item_value') * F('availability_percentage'),output_field=RoundedDecimalField(max_digits=9,decimal_places=3))
                    ),
                    default=Cast(F('item_value') * F('completed_tasks_amount'),output_field=RoundedDecimalField(max_digits=9,decimal_places=3)), 
                    output_field=RoundedDecimalField(max_digits=9,decimal_places=4), # DELIVERABLE employee 
                ),
                availability_discount=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION,
                        then=Cast(-1 + F('availability_percentage'), output_field=RoundedDecimalField(max_digits=4,decimal_places=3)),
                    ),
                    default=Cast(0,output_field=RoundedDecimalField(max_digits=4,decimal_places=3)),
                    output_field=RoundedDecimalField(max_digits=4,decimal_places=3)
                ),
                discount_value=Case(
                    When(user__activity_type=ActivityType.SERVICE_STATION, 
                        then=Cast(F('item_value') - F('subtotal_value'),output_field=RoundedDecimalField(max_digits=9,decimal_places=2)),
                    ),
                    default=Cast(0, output_field=RoundedDecimalField(max_digits=9,decimal_places=2)),
                    output_field=RoundedDecimalField(max_digits=9,decimal_places=2)
                ),

But the values aren't rounded down yet.

2. I've tried to reproduce the same query but passing the output_field as a float and passing a custom validator that parses the number with math.truncate function.

def truncate(value):
        try:
            return math.trunc(value, 2)
        except:
            return 0

And in the annotate values i changed the ouput_field to:

FloatField(validators[truncate])

But i doesn't work too.

Is There a way to round the numbers down in the query? I didn't find anything about this. The django.db.models.Round native function doesn't have the round type as parameter and i couldn't use Func() to create my own... Someone could help me?

Answer 1

Mysql has an "truncate" function to round the values retrieved from queries. You could use an database function with 'Truncate' in the function argument

from django.db.models import Func,F

Func(F('item_value') * F('availability_percentage'), 2, function='Truncate', output_field=FloatField())

Answer 2

Are you restricted to a certain version of django? If not, I believe the documentation references a precision keyword argument to the Round function that might suit your needs. https://docs.djangoproject.com/en/4.1/ref/models/database-functions/#django.db.models.functions.Round

Rounds a numeric field or expression to precision (must be an integer) decimal places. By default, it rounds to the nearest integer. Whether half values are rounded up or down depends on the database.

I misread actually. You already are making use of precision in one of your examples, you're just passing it in as a positional argument.

Looking at your implementation with Decimal I think you have the right idea. However, using prec=2 or prec=3 seems to be referring to the precision of significant digits, so 9809.4069 with prec=2 is 9800 = 9.8 * 10^2 and prec=3 yields 9810 = 9.81 * 10^3.

So, if your numbers are going to be floats, you should use create_decimal as you were, but don't restrict the precision.

context = decimal.Context()
context.create_decimal(9809.4069)

>>> Decimal('9809.468999999999999996023488224')

Then you'd use quantize, as described in the documentation, for the appropriate cut off when dealing with currency. https://docs.python.org/3/library/decimal.html

The quantize() method rounds a number to a fixed exponent. This method is useful for monetary applications that often round results to a fixed number of places