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?

Maybe you could start from the comment model? Comment.objects.filter(post=pk).order_by('-created_at').select_related('post')[:1]. Then you preoloaded the Post object with the most recent comment. This way you get the whole Post and Comment model. — Brobin
– Brobin, Commented Jun 27, 2017 at 15:58
I have a library in the works for this. It's actually not too difficult, at least with Postgres. I'll post an answer once I get it in a clean state. — DylanYoung
– DylanYoung, Commented May 30, 2022 at 2:20

A.Raouf · Accepted Answer · 2022-01-16 10:53:57Z

3

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"))

answered Jan 16, 2022 at 10:53

A.Raouf

2,3801 gold badge26 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

minder · Accepted Answer · 2017-07-06 10:14:56Z

0

You ommited values on the subquery, try this:

Post.objects.annotate(newest_commenter=Subquery(newest.values('author')[:1]))

where 'author' is the "commenter" field on Comment model.

answered Jul 6, 2017 at 10:14

minder

2,1495 gold badges27 silver badges40 bronze badges

1 Comment

jrobs585 Over a year ago

I think that was intended. He wants to annotate the entire object, not just a single 'author' field.

Slava · Accepted Answer · 2021-10-08 16:36:25Z

You can't do it. You can only annotate with a basic type values, not with the object. The basic reason for this is that result of SQL query is a table with all its cells are basic values, not nested tables. In order to do what you want we need SQL to support nested tables and Django to be able to convert nested tables into nested objects. I don't think that's ever going to happen. So, in order to get several fields of related object you should repeat annotation for each field:

Post.objects.annotate(
    newest_commenter_email=Subquery(newest.values('email')[:1]),
    newest_commenter_username=Subquery(newest.values('username')[:1]),
)

Don't be afraid of performance issues due to executing the same query for each field. SQL engine is smart enough to see that query is the same and to cache its value

user1849962 · Accepted Answer · 2021-11-07 14:51:55Z

0

As suggested by Brobin -

Comment.objects.filter(post=pk).order_by('created_at').select_related('post')[:1]

If you really want to go from the Post model though do this -


q = (
  Comment
  .objects
  .filter(
    pk=Subquery(
      Comment
     .objects
     .filter(post=OuterRef('post'))
     .order_by('-created')
     .values('pk')
     [:1]
    )
  )
)

Post.objects.prefetch_related(Prefetch('comment_set', queryset=q)).all()

Unfortunately we cannot slice in the outer query for q because django disallows it. So we slice inside a subquery and then filter on the outer query.

answered Nov 7, 2021 at 14:51

user1849962

1,3632 gold badges11 silver badges16 bronze badges

Collectives™ on Stack Overflow

How to annotate Django QuerySet with other object using Subquery

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related