How to annotate Django QuerySet with other object using Subquery

Question

In Django version 1.11, Subquery expressions were added. I was hoping to use this feature to select a related model object based on some filters.

This is an example from the documentation:

from django.db.models import OuterRef, Subquery
newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')   
Post.objects.annotate(newest_commenter_email=Subquery(newest.values('email')[:1]))

I would like to do the same, but instead of just annotating with the "newest_commenter_email" in this scenario, I'd like the whole Comment object saved to a newest_comment annotation, like so:

from django.db.models import OuterRef, Subquery
newest = Comment.objects.filter(post=OuterRef('pk')).order_by('-created_at')   
Post.objects.annotate(newest_comment=Subquery(newest[:1]))

However, this results in the following error:

FieldError: Expression contains mixed types. You must set output_field

Is there a way around this?

Answer 1

You want to filter of the related model, hence this case I feed Prefetch() is one of the best options I can think about

You can read about it from here Django1.1 Prefetch Objects

and this is an example of what you're looking for

from django.db.models import Prefetch
newest = Comment.objects.filter(do_your_filters_here)   
Post.objects.prefetch_related(Prefetch("comments", queryset=newest, to_attr="newest_comments"))