Django ORM的annotate与order_by为何无法等价于GROUP BY和ORDER BY？

需求背景 #

需要实现与以下SQL等价的Django ORM逻辑：

SELECT tag_id, COUNT(tag_id) AS freq
FROM taggit_taggeditem
WHERE content_type_id IN (
    SELECT id FROM django_content_type
    WHERE app_label = 'reviews'
    AND model IN ('problem', 'review', 'comment')
) AND (
    object_id = 1
    OR object_id IN (
        SELECT id FROM review
        WHERE problem_id = 1
    ) OR object_id IN (
        SELECT c.id FROM comment AS c
        INNER JOIN review AS r
        ON r.id = c.review_id
    )
) 
GROUP BY tag_id
ORDER BY freq DESC;

现有问题代码 #

编写的ORM代码执行后，同一tag_id重复输出且freq始终为1，无法实现SQL中的分组累加效果：

querydict_for_content_type_id = {
    'current_app_label_query' : Q(app_label=ReviewsConfig.name),
    'model_name_query' : Q(model__in=['problem', 'review', 'comment'])
}

# used in content_type_query
query_for_content_type_id = reduce(operator.__and__, querydict_for_content_type_id.values())

# Query relevant content_type from TaggedItem model.    
content_type_query = Q(content_type_id__in=ContentType.objects.filter(query_for_content_type_id))

# Query relevant object_id from TaggedItem model.
object_query = Q(object_id=pk) | Q(object_id__in=problem.review_set.all())
for review in problem.review_set.all():
    object_query |= Q(object_id__in=review.comment_set.all())

tagged_items = TaggedItem.objects.filter(content_type_query&object_query)

# GROUP BY freq ORDER_BY freq DESC;
ordered_tags = tagged_items.annotate(freq=Count('tag_id'))#.order_by('-freq')
for i in ordered_tags:
    print(i.tag_id, i.tag, i.freq)

问题原因 #

Django ORM中，直接对QuerySet调用annotate()时，会为每一行记录添加注解字段，并不会自动按tag_id分组。只有先通过values('tag_id')指定分组字段，ORM才会生成GROUP BY tag_id的SQL逻辑，否则每个TaggedItem行都会被单独统计，freq自然为1，重复输出的就是所有匹配的原始行。

修复方案 #

1. 修正分组统计逻辑 #

先通过values('tag_id')指定分组字段，再执行annotate和order_by，同时可以通过关联查询获取标签的详细信息。

2. 优化object_query避免N+1查询 #

原来的循环遍历review_set查询comment_set会产生多次数据库查询，改用关联查询一次性获取所有相关评论ID。

修改后的完整代码 #

from django.db.models import Q, Count
from django.contrib.contenttypes.models import ContentType
from taggit.models import TaggedItem

# 构造content_type查询条件
content_types = ContentType.objects.filter(
    app_label='reviews',
    model__in=['problem', 'review', 'comment']
)
content_type_query = Q(content_type_id__in=content_types)

# 构造object_id查询条件，优化为一次性关联查询
review_ids = Review.objects.filter(problem_id=pk).values_list('id', flat=True)
comment_ids = Comment.objects.filter(review__problem_id=pk).values_list('id', flat=True)
object_query = Q(object_id=pk) | Q(object_id__in=review_ids) | Q(object_id__in=comment_ids)

# 执行分组统计：先指定分组字段，再统计排序
ordered_tags = TaggedItem.objects.filter(content_type_query & object_query)\
    .values('tag_id', 'tag__name')  # 按需添加Tag模型的字段，如name、slug
    .annotate(freq=Count('tag_id'))\
    .order_by('-freq')

# 输出结果
for item in ordered_tags:
    print(item['tag_id'], item['tag__name'], item['freq'])

说明 #

• values('tag_id')会触发ORM生成GROUP BY tag_id的SQL，此时annotate(Count('tag_id'))会统计每组的记录数，完全对应原SQL的聚合逻辑
• 如果需要更多Tag模型的字段，直接在values中添加关联字段即可，比如tag__slug
• 优化后的object_query通过关联查询一次性获取所有目标object_id，避免了循环带来的性能损耗

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