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

user_10219385 0
Viewed 2755

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":""

annotate django-rest-framework null django string
1 Answers

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, Coalesce, Value

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