Django query bases on greater date

user_2043406 0
Viewed 49

I want to know how efficient this filter can be done with django queries. Essentially I have the followig two clases

class Act(models.Model):
    Date = models.DateTimeField()
    Doc = models.ForeignKey(Doc)
    ...

class Doc(models.Model):
    ...

so one Doc can have severals Acts, and for each Doc I want to get the act with the greater Date. I'm only interested in Acts objects.

For example, if a have

act1 = (Date=2021-01-01, Doc=doc1)
act2 = (Date=2021-01-02, Doc=doc1)
act3 = (Date=2021-01-03, Doc=doc2)
act4 = (Date=2021-01-04, Doc=doc2)
act5 = (Date=2021-01-05, Doc=doc2)

I want to get [act2, act5] (the Act with Doc=doc1 with the greater Date and the Act with Doc=doc2 with the greater Date).

My only solution is to make a for over Docs.

Thank you so much

django-queryset django-models
1 Answers

You can do this with one or two queries: the first query will retrieve the latest Act per Doc, and then the second one will then retrieve the acts:

from django.db.models import OuterRef, Subquery

last_acts = Doc.objects.annotate(
    latest_act=Subquery(
        Act.objects.filter(
            Doc_id=OuterRef('pk')
        ).values('pk').order_by('-Date')[:1]
    )
).values('latest_act')

and then we can retrieve the corresponding Acts:

Act.objects.filter(pk__in=last_acts)

depending on the database, it might be more efficient to first retrieve the primary keys, and then make an extra query:

Act.objects.filter(pk__in=list(last_acts))
user_675790
Related