How to write sql COALESCE in Django

Question

I'm new to Django. How to write COALESCE sql queryset in to django orm.

query = 'SELECT COALESCE(max(CAST(order_no as UNSIGNED)), 0) as o,id from nanossc_Sales_master'

models.py

class Sales_master(models.Model):
    slno = models.IntegerField(blank=True, null=True)
    order_no = models.CharField(max_length=50, blank=True, null=True)
    type = models.CharField(max_length=50, blank=True, null=True)
    customer_id = models.CharField(max_length=50, blank=True, null=True)
    customer_name = models.CharField(max_length=50, blank=True, null=True)
    brand_name = models.CharField(max_length=50, blank=True, null=True)
    name = models.CharField(max_length=50, blank=True, null=True)

Answer 1

You can use an Coalesce expression ^[Django-doc] and Cast expression ^[Django-doc] here:

from django.db.models import Max, PositiveIntergerField, Value
from django.db.models.functions import Cast, Coalesce

Sales_master.objects.aggregate(
    o=Coalesce(Max(Cast('order_no', output_field=PositiveIntergerField())), Value(0))
)

This will produce a dictionary where a key 'o' maps to the result for this aggregate.