Fetch latest related objects in django

Question

In my django app I have 'Documents'. Each document has one or more 'Revisions' that are ordered by date created. I'd like a way to get the latest revision for every document. The best I have so far is the code below, but I'm thinking there must be a way to do this with less database queries?

def get_document_templates():
    result = []
    for d in Document.objects.filter(is_template=True).all():
        result.append(d.documentrevision_set.latest())
    return result

I've been investigating 'annotate' and 'aggregate' filters, but can't figure out how to do this more efficiently. I'd rather not get my hands dirty in raw SQL, as the database backend may be changing in the near future. Anyone have any ideas ?

thanks!!

http://blog.roseman.org.uk/2010/08/14/getting-related-item-aggregate/ — Timmy O'Mahony, Aug 15 '13 at 09:51
Ah, thanks Tim that does help some. Looking at it, it does seem impossible without some SQL injection. — bharling, Aug 15 '13 at 09:54
Tim, if you want to answer with the above I'd be happy to accept. — bharling, Aug 15 '13 at 09:56

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

I think there are two approaches. One explained this this blog post "Getting the related item in an aggregate". This will get you each Document with an attached 'Revision' (if you need access to both).

If you only want the Revision, you could try making use of the values() method. It's functionality changes subtly when used with aggregations:

As with the filter() clause, the order in which annotate() and values() clauses are applied to a query is significant. If the values() clause precedes the annotate(), the annotation will be computed using the grouping described by the values() clause.

However, if the annotate() clause precedes the values() clause, the annotations will be generated over the entire query set. In this case, the values() clause only constrains the fields that are generated on output.

So you could do a grouping of the Revision by Document and aggregate on date

Revision.objects.all().values('document').aggregate(Max('date'))

Unfortunately - that code returns an empty queryset for my models, but I'm accepting based on the link supplied, thanks! — bharling, Aug 15 '13 at 12:44

score -1 · Answer 2 · answered Aug 15 '13 at 09:36

-1

you can fetch latest 9 data from a model like this:

Model.objects.all().order_by('-date')[0:9]

answered Aug 15 '13 at 09:36

nim4n

1,813
3
21
36

This isn't what he is asking – Timmy O'Mahony Aug 15 '13 at 09:52
I already have a 'get_latest_by' clause in the Meta class for document revisions, so finding the latest one is no problem. However I need to combine this with the 'parent' document models and get the latest revision for each doc. – bharling Aug 15 '13 at 09:55

Fetch latest related objects in django

2 Answers2

Linked