19

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?

funnydman
  • 9,083
  • 4
  • 40
  • 55
SAPikachu
  • 579
  • 1
  • 3
  • 17
  • Could you annotate chapters with novels instead? – John Mee Aug 27 '12 at 03:13
  • Seems not possible to me, because I need only the latest chapter for each novel, but a novel have many chapters, and chapters are in different volumes. As far as I know `distinct()` is useless in this case (correct me if I am wrong), and I don't know any other way to select exactly one chapter except for starting from novel. Any ideas? – SAPikachu Aug 27 '12 at 04:10

4 Answers4

33

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.

Rune Kaagaard
  • 6,643
  • 2
  • 38
  • 29
12

Possible with Django 3.2+

Make use of django.db.models.functions.JSONObject (added in Django 3.2) to combine multiple fields (in this example, I'm fetching the latest object, however it is possible to fetch any arbitrary object provided that you can get LIMIT 1) to yield your object):

MainModel.objects.annotate(
    last_object=RelatedModel.objects.filter(mainmodel=OuterRef("pk"))
    .order_by("-date_created")
    .values(
        data=JSONObject(
            id="id", body="body", date_created="date_created"
        )
    )[:1]
)
suayip uzulmez
  • 628
  • 8
  • 23
  • Oh, I must have totally missed JSONObject - I've just been using my own Func subclass and JsonBuildObject, but I think this is much neater...although sometimes my keys are field values too, rather than fixed keys. – Matthew Schinckel Nov 15 '22 at 04:03
  • What if I want to later through django rest api sort or filter using this field? – yashas123 Nov 20 '22 at 10:11
4

No, it's not possible to combine them into a single query.

You can read the following blog post to find two workarounds.

thikonom
  • 4,219
  • 3
  • 26
  • 30
4

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.

mrgreytop
  • 74
  • 4
  • 3
    Unfortunately does not work for me as it seems subqueries can only return a single column and not a complete object. – Gnietschow Dec 01 '20 at 13:32
  • 1
    @Gnietschow, Yes if you want more than just one column you are probably better off using something like prefetch_related [docs](https://docs.djangoproject.com/en/3.1/ref/models/querysets/#prefetch-related) – mrgreytop Dec 01 '20 at 20:57
  • Unfortunately that does not work for me as I want to sort and filter for fields of both models. – Gnietschow Dec 02 '20 at 10:24
  • 1
    @Gnietschow See my answer that yields multiple columns in a single subquery. – suayip uzulmez Jul 23 '22 at 07:32