Django aggregation query on related one-to-many objects

Question

Here is my simplified model:

class Item(models.Model):
    pass

class TrackingPoint(models.Model):
    item = models.ForeignKey(Item)
    created = models.DateField()
    data = models.IntegerField()

    class Meta:
        unique_together = ('item', 'created')

In many parts of my application I need to retrieve a set of Item's and annotate each item with data field from latest TrackingPoint from each item ordered by created field. For example, instance i1 of class Item has 3 TrackingPoint's:

tp1 = TrackingPoint(item=i1, created=date(2010,5,15), data=23)
tp2 = TrackingPoint(item=i1, created=date(2010,5,14), data=21)
tp3 = TrackingPoint(item=i1, created=date(2010,5,12), data=120)

I need a query to retrieve i1 instance annotated with tp1.data field value as tp1 is the latest tracking point ordered by created field. That query should also return Item's that don't have any TrackingPoint's at all. If possible I prefer not to use QuerySet's extra method to do this.

That's what I tried so far... and failed :(

Item.objects.annotate(max_created=Max('trackingpoint__created'),
                      data=Avg('trackingpoint__data')).filter(trackingpoint__created=F('max_created'))

Any ideas?

Answer 1

This isn't directly answer to your question, but in case don't need exactly what you described you might be interested in greatest-n-per-group solution. You can take a look on my answer on similar question:

http://stackoverflow.com/questions/2074514/django-query-that-get-most-recent-objects-from-different-categories/2076665#2076665

-- this should apply directly to your case:

 items = Item.objects.annotate(tracking_point_created=Max('trackingpoint__created')) 
 trackingpoints = TrackingPoint.objects.filter(created__in=[b.tracking_point_created for b in items])

Note that second line can produce ambiguous results if created dates repeat in TrackingPoint model.