Annotate returning null on non-existent values and I want to convert this to null into empty string

Question

I have two models , Customer and Purchase. I want to return the last store that the customer purchased from and order on this.

I can successfully show this list, showing last store the Customer purchased from, and being able to order on store name, using the following models, query/subquery.

# models
class Customer(models.Model):

    name = models.CharField(max_length=30)

class Purchase(models.Model):

    store = models.CharField(max_length=30)
    amount = models.DecimalField(decimal_places=2,max_digits=6)
    customerID = models.ForeignKey(Customer, on_delete=models.CASCADE, 
                                   related_name='purchases')
    order_date = models.DateField()

#viewset

#subquery
purchase_qs = Purchase.objects.filter(customerID=OuterRef("pk")).order_by("-order_date")

queryset = Customer.objects.all().annotate(last_purchase=Subquery(purchase_qs.values('store')[:1]))

ordering_fields = ['last_purchase']

My Current Output for Customers who have zero Purchases is.

"last_purchase":null

I want to have

"last_purchase":""

score 3 · Answer 1 · edited Apr 25 '23 at 04:56

ForeignKey fields automatically append _id to the name of the model field, so you'd need to use customerId_id to reference the ForeignKey. Clearly this isn't what you want, so I'd recommend renaming the field to customer instead, also I think this is why your query is blank.

With that being said, you don't really need Subquery or OuterRef for this. Instead you can use the reverse relation of your Customer model, along with Max:

from django.db.models import Max

Customer.objects.select_related('Purchase').annotate(
    last_purchase = Max('purchases__order_date')
)

Lastly, the null key is False by default so no need to say null=False, and it doesn't make sense to have blank=True but null=False. See this question for an excellent explanation about what the differences between null and blank are.

Update

The Coalesce function seems to be perfect for this scenario:

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

Customer.objects.select_related('Purchase').annotate(
    last_purchase = Coalesce(Max('purchases__order_date', Value(''))
)

Yes, your example worked to, so a subqery is not necessary. I still have the issue of null getting returned on Customers who have not purchases. I had used the serializer 'to_representation' method to change null to empty string, but I want to have the value as empty string before it gets to the serializer. I guess I am looking for a conditional statement on the annotate, suche as Case-When, but I cannot get that to work. — raniela, Oct 18 '19 at 07:37
The Coalesce worked and returned the empty string instead of null, thus avoiding doing this in the serialiser. — raniela, Oct 23 '19 at 04:09

Annotate returning null on non-existent values and I want to convert this to null into empty string

1 Answers1

Update