Django: Reference to an outer query may only be used in a subquery

Question

I use the following queries:

def get_queryset(self):
    posts = Post.objects.filter(topic_id=OuterRef('pk'))
    unread_posts = posts.exclude(read_by=self.request.user)
    return Topic.objects.all().annotate(is_unread=Exists(unread_posts),
                                        number_of_replies=Subquery(posts.count())).order_by('-latest_post')

Unfortunately, I get the following error message:

This queryset contains a reference to an outer query and may only be used in a subquery.

I'm confused because I explicitly set Subquery(posts.count()).

Can someone give me a hint?

Answer 1

The problem is that .count() is something that evaluates the query eagerly, and thus Subquery(..) is never used. But even if that would work, it is not a good idea to do so anyway.

You can just count with a JOIN, like:

from django.db.models import Count, Exists, OuterRef

def get_queryset(self):
    posts = Post.objects.filter(topic_id=OuterRef('pk'))
    unread_posts = posts.exclude(read_by=self.request.user)
    return Topic.objects.annotate(
        is_unread=Exists(unread_posts),
        number_of_replies=Count('post')
    ).order_by('-latest_post')