CODEWITHSUNDEEP
djangoaggregatedjango-queryset
bdoubleu
bdoubleu

Reputation: 6107

Django format DecimalField in query / annotation

Is it possible to format a DecimalField in an annotation similar to intcomma template tag?

class Product(models.Model):
    plu = models.CharField(max_length=8)
    description = models.CharField(max_length=255)
    price = models.DecimalField(max_digits=9, decimal_places=2, default=D('0'))

For example if instance.price = Decimal('1000') the annotated attribute would return the number formatted with a comma separator (1,000.00)

I'm creating PDF reports in ReportLab and table data is a list of lists. If the queryset could return the comma separated number there would be speed improvements rather than having to iterate over each object in the queryset to apply the formatting.

from django.contrib.humanize.templatetags.humanize import intcomma

products = Product.objects.all()
table_data = [['PLU', 'Description', 'Price']]
for product in products:
    table_data.append([product.plu, product.description, intcomma(product.price)])

Could be simplified to

table_data = [['PLU', 'Description', 'Price']] + list(
    Product.objects.all()
    .annotate(comma_sep_price=...)
    .values_list('plu', 'descripton', 'comma_sep_price')
)

Upvotes: 2

Views: 1693

Answers (1)

bdoubleu
bdoubleu

Reputation: 6107

I was able to solve this using the PostgreSQL data type formatting function TO_CHAR.

Using an ExpressionWrapper:

from django.db.models import CharField, ExpressionWrapper, Func, F, Value

data = (
    Product.objects
    .annotate(
        formatted_price=ExpressionWrapper(
            Func(F('price'), Value('FM999,999,999.00'), function='TO_CHAR'),
            output_field=CharField()
        )
    )
    .values_list('plu', 'description', 'formatted_price')
)

Or as a reusable custom query expression:

class CommaSeparated(Func):
    function = 'TO_CHAR'
    template = "%(function)s(%(expressions)s, 'FM999,999,999.00')"

data = (
    Product.objects
    .annotate(formatted_price=CommaSeparated(F('price'), output_field=CharField()))
    .values('plu', 'description', 'formatted_price')
)

Upvotes: 2

Related Questions