Conditional annotations with Aggregation over only some fields in Django

So lets assume i have two databases in my Django project

class Article(models.Model):
  name = models.CharField(max_length=200)
  # ..


class Price(models.Model):
  article = models.ForeignKey('Article')
  date = models.DateTimeField(auto_now_add=True)
  price = models.DecimalField()
  # ..

There exist multiple Price entries per day for the same article.

Now I want to annotate an article queryset with the average price of every article on the previous day. But I have no idea on how to do this in one efficient query.

What I have done is this:

articles = Articles.objects.all().select_related().filter(price__date__exact=datetime.datetime.now() - datetime.timedelta(days=1)).annotate(avg_price=Avg('price__price'))

This works, if every article would have at least one price each day. But that isnt always the case. Articles that have no price for the previous day should have None or 0 or some default as avg_price.

Does anybody know how to achieve this?

Answer

Aggregation functions can take an argument filter [Django docs] which can be used to put conditions on the aggregation:

from django.db.models import Q

articles = Articles.objects.all().select_related().annotate(
    avg_price=Avg(
        'price__price',
        filter=Q(price__date__exact=datetime.datetime.now() - datetime.timedelta(days=1))
    )
)