Django aggregation query on related one-to-many objects

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?开发者_JAVA技巧

Here's a single query that will provide (TrackingPoint, Item)-pairs:

TrackingPoint.objects.annotate(max=Max('item__trackingpoint__created')).filter(max=F('created')).select_related('item').order_by('created')

You would have to query for items without TrackingPoints separately.

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:

Django Query That Get Most Recent Objects From Different Categories

-- 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.