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:

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

CodePudding user response：

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.

CodePudding user response：

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).