Annotating django QuerySet with values from related table

Question

I have a model Page with 2 related models PageScoreHistory and PageImageHistory, where both history models have page field as a ForeignKey to Page and have a date field. The problem I am having, is that when I get a list of PageImageHistory QuerySet, I want to be able to retrieve the score the page had at the time the image was taken. For an individual PageImageHistory object, I can do this easily:

pih = PageImageHistory.objects.get(id=123)
score = pih.page.pagescorehistory_set.filter(datetime__lte=pih.captured).latest('datetime')

But for a QuerySet, I would like to avoid running a query for each member of the set. Is there a wayo to do this is a sane fashion, without actually looping through all members of the set?

Answer 1

It's hard without seeing your models, but does this do what you want?

PageImageHistory.objects.filter(
    page__pagescorehistory__datetime__lte=x.captured
).annotate(Max('page__pagescorehistory__datetime')