Django prefetch_related传注解查询集后无法按关联字段过滤报错

你定义了如下三个模型：

class User:
    screen_name = Charfield


class Post:
    author = FK(User)


class Comment:
    post = FK(Post, related_name="comment_set")
    author = FK(User)

需要按用户身份逻辑给查询集添加字段注解，核心实现代码如下：

if is_student:
        comment_qs = Comment.objects.annotate(
            comment_author_screen_name_seen_by_user=Case(
                When(Q(is_anonymous=True) & ~Q(author__id=user.id), then=Value("")),
                default=F("author__screen_name"), output_field=CharField()
            ),
            comment_author_email_seen_by_user=Case(
                When(Q(is_anonymous=True) & ~Q(author__id=user.id), then=Value("")),
                default=F("author__email"), output_field=CharField()
            ),
        )
        queryset = queryset.annotate(
            post_author_screen_name_seen_by_user=Case(
                When(Q(is_anonymous=True) & ~Q(author__id=user.id), then=Value("")),
                default=F("author__screen_name"), output_field=CharField()
            ),
            post_author_email_seen_by_user=Case(
                When(Q(is_anonymous=True) & ~Q(author__id=user.id), then=Value("")),
                default=F("author__email"), output_field=CharField()
            ),
        )
    else:
        comment_qs = Comment.objects.annotate(
            comment_author_screen_name_seen_by_user=F("author__screen_name"),
            comment_author_email_seen_by_user=F("author__email")
        )
        queryset = queryset.annotate(
            post_author_screen_name_seen_by_user=F("author__screen_name"),
            post_author_email_seen_by_user=F("author__email"),
        )
queryset = queryset.prefetch_related(Prefetch("comment_set", queryset=comment_qs))

配置完成后，尝试通过comment_set__comment_author_screen_name_seen_by_user字段过滤Post查询集时抛出如下错误：

django.core.exceptions.FieldError: Unsupported lookup 'comment_author_screen_name_seen_by_user' for AutoField or join on the field not permitted

但直接获取实例后可以正常访问该注解字段：

queryset[0].comment_set.all()[0].comment_author_screen_name_seen_by_user == "Foo Bar"

• 报错核心原因是Django prefetch_related的执行机制和预期不符：prefetch_related不会在数据库层面做JOIN合并查询，而是拆成两条独立SQL执行：第一条查询Post主表数据，第二条拿所有Post的主键去查关联的Comment表数据，最后在Python内存层面把Comment和对应的Post做绑定。
• 你给comment_qs加的注解，只存在于第二条查Comment的独立SQL中，Post主查询的SQL完全感知不到这些注解字段，自然不支持用comment_set__comment_author_screen_name_seen_by_user这种跨表lookup过滤Post。
• 实例访问正常只是因为内存中挂载的Comment实例本身带了注解属性，属于Python层面的属性访问，和数据库层面的查询过滤是完全独立的两个流程。

要实现按评论的注解字段过滤Post，不能只把注解写在Prefetch的子查询里，需要把过滤用到的注解直接加到Post主查询集上，Prefetch保留用于后续高效获取评论列表即可，参考实现：

from django.db.models import Case, When, Q, Value, F, CharField

# 抽离复用的判断条件，减少重复代码
post_anonymous_cond = Q(is_anonymous=True) & ~Q(author__id=user.id)
comment_anonymous_cond = Q(comment_set__is_anonymous=True) & ~Q(comment_set__author__id=user.id)

if is_student:
    # 主查询直接跨表注解评论的可见字段，用于过滤
    queryset = queryset.annotate(
        post_author_screen_name_seen_by_user=Case(
            When(post_anonymous_cond, then=Value("")),
            default=F("author__screen_name"),
            output_field=CharField()
        ),
        post_author_email_seen_by_user=Case(
            When(post_anonymous_cond, then=Value("")),
            default=F("author__email"),
            output_field=CharField()
        ),
        comment_author_screen_name_seen_by_user=Case(
            When(comment_anonymous_cond, then=Value("")),
            default=F("comment_set__author__screen_name"),
            output_field=CharField()
        )
    )
    # 此时可以正常用注解字段过滤Post
    queryset = queryset.filter(comment_author_screen_name_seen_by_user__icontains="关键词")

    # Prefetch子查询的注解保留，用于访问post.comment_set时拿到正确的字段
    comment_qs = Comment.objects.annotate(
        comment_author_screen_name_seen_by_user=Case(
            When(Q(is_anonymous=True) & ~Q(author__id=user.id), then=Value("")),
            default=F("author__screen_name"),
            output_field=CharField()
        ),
        comment_author_email_seen_by_user=Case(
            When(Q(is_anonymous=True) & ~Q(author__id=user.id), then=Value("")),
            default=F("author__email"),
            output_field=CharField()
        ),
    )
else:
    queryset = queryset.annotate(
        post_author_screen_name_seen_by_user=F("author__screen_name"),
        post_author_email_seen_by_user=F("author__email"),
        comment_author_screen_name_seen_by_user=F("comment_set__author__screen_name")
    )
    queryset = queryset.filter(comment_author_screen_name_seen_by_user__icontains="关键词")
    comment_qs = Comment.objects.annotate(
        comment_author_screen_name_seen_by_user=F("author__screen_name"),
        comment_author_email_seen_by_user=F("author__email")
    )

queryset = queryset.prefetch_related(Prefetch("comment_set", queryset=comment_qs))

如果你的需求不是过滤Post，只是过滤每个Post下关联的评论列表，那直接在comment_qs里加filter即可，不需要修改主查询，但这种方式不会排除没有符合条件评论的Post，和过滤父查询集的效果有本质区别。

内容的提问来源于stack exchange，提问作者Yura Khkyan