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?
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)) ) )