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?
In the Django framework, both annotate and aggregate are responsible for identifying a given value set summary. Among these, annotate identifies the summary from each of the items in the queryset. Whereas in the case of aggregate, the summary is calculated for the entire queryset.
The Django ORM is a convenient way to extract data from the database, and the annotate() clause that you can use with QuerySets is also a useful way to dynamically generate additional data for each object when the data is being extracted.
Q object encapsulates a SQL expression in a Python object that can be used in database-related operations. Using Q objects we can make complex queries with less and simple code. For example, this Q object filters whether the question starts wiht 'what': from django.
In the Django QuerySet API, F() expressions are used to refer to model field values directly in the database.
Yes, using Subqueries, docs: https://docs.djangoproject.com/en/3.0/ref/models/expressions/#subquery-expressions
latest_chapters = Chapter.objects.filter(novel = OuterRef("pk"))\
.order_by("chapter_order")
novels_with_chapter = Novel.objects.annotate(
latest_chapter = Subquery(latest_chapters.values("chapter")[:1]))
Tested on Django 3.0
The subquery creates a select statement inside the select statement for the novels, then adds this as an annotation. This means you only hit the database once.
I also prefer this to Rune's answer as it actually annotates a Novel object.
Hope this helps, anyone who came looking like much later like I did.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With