Use ifnull default in django aggregation

Question

I have the following model classes:

class Goods(models.Model):
    name = models.CharField(max_length=100)

class InRecord(models.Model):
    goods = models.ForeignKey(Goods, related_name='in_records')
    timestamp = models.DateTimeField()
    quantity = models.IntegerField()

class OutRecord(models.Model):
    goods = models.ForeignKey(Goods, related_name='out_records')
    timestamp = models.DateTimeField()
    quantity = models.IntegerField()

So, I want to get a QuerySet which contains all the goods having a positive repository.

Another way to describe it, I want to filter Goods which has a greater InRecord quantity summary than OutRecord summary.

---

What I've tried:

First, I use annotate to add the summary to the queryset:

qs = Goods.objects.annotate(
        qty_in=Sum(in_records__quantity), 
        qty_out=Sum(out_records_quantity)
    )

This seemed works, but have one problem, when there is no relative in_records or out_records of some goods, the fields annotated returns None.

Question: So, is there any way for me to set a default in this case, just like a ifnull(max(inreocrd.quantity), 0)* call in sql?

---

After this, I want to add a filter on that QuerySet:

I tried:

qs = qs.filter(qty_in__gt(F(qty_out)))

But still if there is no records on a goods, It doesn't work.

Please help.

Answer 1

You can use Django's Coalesce function. Something like this should work in Django 1.8 or later:

from django.db.models.functions import Coalesce

qs = Goods.objects.annotate(
        qty_in=Sum(Coalesce(in_records__quantity, 0)),
        qty_out=Sum(Coalesce(out_records__quantity, 0))
    )