1

I have the following model:

class Example(models.Model):
    project_id = models.IntegerField(
        null=False, 
        blank=False, 
        default=0,
    )
    field1 = models.CharField(
        max_length=250,
        null=True, 
        blank=True,
    )
    field2 = models.CharField(
        max_length=250,
        null=True, 
        blank=True,
    )
    total = models.CharField(
        max_length=250,
        null=True, 
        blank=True,
    )

Example data:

project_id field1 field2 total
1 1,323 4,234.55 5,557.55
2 1,000 2 1,002
3 1.23 3 4.23

total = field1 + field2

I would like to sum all total values.

This is what I've tried views.py:

context['total'] = Example.objects.filter(project_id=pid).aggregate(Sum('total'))

Current output:

{'total': 10.23}

Expected output:

{'total': 6,563.78}

Or, if that's not possible at least: 6563.78 so that I can format the numbers later.

Since the project requires thousand comma separator and decimal points, I can not change or alter the model fields and use FloatField.

Any help would be much appreciated

Cheknov
  • 1,892
  • 6
  • 28
  • 55
  • 1
    Don't use `CharField`s to store numeric data. The comma separators should be rendered by the template, *not* stored in the database. – Willem Van Onsem Apr 25 '22 at 09:02
  • I know, but I can't change the model fields unfortunately. – Cheknov Apr 25 '22 at 09:02
  • "Since the project requires thousand comma separator and decimal points, I can not change or alter the model fields and use FloatField" Can you elaborate on this please? – Chillie Apr 25 '22 at 09:25
  • So basically there are other parts in the project that I'm not allowed to modify and those parts get the numbers (as CharFields). If I change the field types to FloatFields, other parts of the project will stop working. – Cheknov Apr 25 '22 at 09:44

2 Answers2

2

Since you say you can't change the data type on the fields themselves, you can achieve this by using the Replace and Cast database functions (of course, this isn't ideal - it would be better to fix the data types in the model itself).

This should work - at least it does on a production-grade database like PostgreSQL (I am not sure it will work on SQLite - if you are using that in production then you really have problems):

from django.db.models import FloatField, Value
from django.db.models.functions import Cast, Replace

context['total'] = Example.objects.annotate(
    cleaned_total=Replace('total', Value(','), Value(''))
).annotate(
    float_total=Cast('cleaned_total', FloatField())
).aggregate(Sum('float_total'))

What this is doing is:

  1. Performing a replace on the field to remove commas.
  2. Passing that cleaned value to a cast function that casts the cleaned strings as floats.
  3. Performing a sum on the result.
solarissmoke
  • 30,039
  • 14
  • 71
  • 73
  • Should we add directly looping through the queryset in python and additional model fields that populate on `save` as alternatives? – Chillie Apr 25 '22 at 10:06
  • @Chillie feel free to add an answer with those two alternatives - both of which would work (although looping in Python is going to be very inefficient). – solarissmoke Apr 25 '22 at 10:11
0

Here are alternatives that you could look at in case solarissmoke's answer is not what you're looking for:

  1. Since aggregation/annotation needs real database fields, you could give up on aggregation/database functions and loop through the query set in python and convert the values to required numeric types (prehaps with a model method?) and sum them up. This does come with noticeably worse performance.

  2. You could create additional numeric model fields that populate automatically after save (possibly by overriding the deffault save behaviour or using signals ). And then use aggregation and Sum on those numeric fields. You have to be careful about the possible conflicts with the original CharFields that would cause your results to be incorrect (i.e. missed updates, some other custom dehaviour).

Chillie
  • 1,356
  • 13
  • 16