Django annotated queryset with latest values for some fields

Question

I have a model which store some marketing performances:

class DailyPerformance(models.Model):

    class Meta:
        unique_together = ('route_hash_value', 'datetime_tz')
        ordering = ('-datetime_tz',)

    route_hash_value = models.CharField(max_length=255, db_index=True, editable=False)
    datetime_tz = models.DateTimeField(db_index=True, editable=False)
    network = models.ForeignKey('administration.Network', on_delete=models.PROTECT, null=True)
    account = models.ForeignKey('administration.Account', on_delete=models.SET_NULL, null=True)

    # Properties
    budget = models.FloatField(null=True, db_index=True)
    bid = models.FloatField(null=True, db_index=True)
    status = models.CharField(max_length=255, null=True, db_index=True)

    # METRICS
    cost_amount = models.FloatField(null=True, db_index=True)
    impressions = models.IntegerField(null=True, db_index=True)
    clicks_in = models.IntegerField(null=True, db_index=True)
    clicks_out = models.IntegerField(null=True, db_index=True)
    revenue_amount_net = models.FloatField(null=True, db_index=True)
    _profit = models.FloatField(null=True, db_index=True)
    _roi = models.FloatField(null=True, db_index=True)
    _ctr = models.FloatField(null=True, db_index=True)

And I need to generate a queryset for the drf ModelViewset which aggregate numeric metrics and get the last values for the fields: bid, budget and status.

Something like this:

class DailyPerformanceHViewSet(viewsets.ReadOnlyModelViewSet):

    def get_queryset(self):
        queryset = DailyPerformance.objects.values(
            'route_hash_value',
            'datetime_tz',
            'network',
            'account',
        ).annotate(
            cost_amount=Round(Sum('cost_amount'), 3),
            revenue_amount_net=Round(Sum('revenue_amount_net'), 3),
            impressions=Sum('cost_impressions', output_field=FloatField()),
            clicks_in=Sum('cost_clicks_in', output_field=FloatField()),
            clicks_out=Sum('cost_clicks_out', output_field=FloatField()),
            _profit=Round(Sum('_profit'), 3),
            _roi=Round(F('_profit') / F('cost_amount'), 3),
            _ctr=Round(F('clicks_out') / F('clicks_in'), 3),
            ### 
            budget=...,      # <== LATEST BUDGET ORDERED BY -datetime_tz 
            bid=...,      # <== LATEST BID ORDERED BY -datetime_tz 
            status=...,    # <== LATEST STATUS ORDERED BY -datetime_tz 
        )

        return queryset

but I really don't know how to do that

question from:https://stackoverflow.com/questions/66050837/django-annotated-queryset-with-latest-values-for-some-fields

Answer 1

Hi you may need to perform a subquery to annotate the latest budget etc onto your queryset.

# this will create a query to select DailyPerformance objects and order by date.
last_budget_entry = DailyPerformance.objects.filter(id=OuterRef('pk')).order_by('-datetime_tz)

# annotate your queryset with the query first item in the query(since its the newest). 
queryset.annotate(latest_budget=Subquery(last_budget_entry.values('id')[:1]))

this will create a subquery which will evaluate the the id of the first element in the "last_budget_entry" query. (this value will be provided by Outeref) and annotate the value of the id to the queryset.

#to annotate the value of the budget field 
queryset.annotate(latest_budget=Subquery(last_budget_entry.values('budget')[:1]))

You can order the query with the outerref to select the value you want annotated on the new queryset Read about query expressions and subqueries here https://docs.djangoproject.com/en/3.1/ref/models/expressions/#subquery-expressions