[Fixed]-Django query expression for calculated fields that require conditions and casting

by

15👍

✅

Use conditional expressions:

from django.db.models import Case, F, Sum, When

Stats.objects.values('product').annotate(
    tot_impressions=Sum('impressions'), 
    tot_clicks=Sum('clicks')
).annotate(
    ctr=Case(When(tot_impressions=0, then=None),  # or other value, e.g. then=0
             # 1.0*... is to get float in SQL
             default=1.0*F('tot_clicks')/F('tot_impressions'),
             output_field=models.FloatField())
).order_by('ctr')
👤vsd

0👍

The exact math on how you scale this is up to you, but adding in a python constant inside a nested ExpressionWrapper should suffice:

ctr = models.ExpressionWrapper(
    models.F('clicks')/models.ExpressionWrapper(
        models.F('impressions') + 1, output_field = models.FloatField()
    ),
    output_field = models.FloatField()
)
👤user764357

0👍

You can use the django-pg-utils package for the divide query expression that handles division by zero and returns float values

from pg_utils import divide

Stats.objects.annotate(ctr=divide(F('clicks'), F('impressions')))
👤arjunattam

Leave a comment