In django, is there a way to directly annotate a query with a related object in single query?

python django django-orm annotate
SAPikachu picture SAPikachu · Aug 27, 2012 · Viewed 11.8k times · Source

Consider this query:

query = Novel.objects.< ...some filtering... >.annotate(
    latest_chapter_id=Max("volume__chapter__id")
)

Actually what I need is to annotate each Novel with its latest Chapter object, so after this query, I have to execute another query to select actual objects by annotated IDs. IMO this is ugly. Is there a way to combine them into a single query?

Answer

Rune Kaagaard picture Rune Kaagaard · Jan 14, 2015

Yes, it's possible.

To get a queryset containing all Chapters which are the last in their Novels, simply do:

from django.db.models.expressions import F
from django.db.models.aggregates import Max

Chapters.objects.annotate(last_chapter_pk=Max('novel__chapter__pk')
    ).filter(pk=F('last_chapter_pk'))

Tested on Django 1.7.

Related questions

How to perform OR condition in django queryset?

I want to write a Django query equivalent to this SQL query: SELECT * from user where income >= 5000 or income is NULL. How to construct the Django queryset filter? User.objects.filter(income__gte=5000, income=0) This doesn't work, because it …

Read More
What's the difference between select_related and prefetch_related in Django ORM?

In Django doc, select_related() "follows" foreign-key relationships, selecting additional related-object data when it executes its query. prefetch_related() does a separate lookup for each relationship, and does the "joining" in Python. What does it mean by "doing the joining …

Read More
How to create an object for a Django model with a many to many field?

My model: class Sample(models.Model): users = models.ManyToManyField(User) I want to save both user1 and user2 in that model: user1 = User.objects.get(pk=1) user2 = User.objects.get(pk=2) sample_object = Sample(users=user1, users=user2) sample_object.…

Read More