Django中如何按列值过滤将符合条件的多行聚合为单行

现有通知模型定义 #

class Notification(models.Model):
    class VerbChoices(models.TextChoices):
        COMMENTED = 'commented'
        LIKE = 'like'

    id = models.CharField(primary_key=True, default=generate_id, max_length=255)
    actor = models.ForeignKey(Users, on_delete=models.DO_NOTHING, related_name='notify_actions', null=True)
    recipient = models.ForeignKey(Users, on_delete=models.DO_NOTHING, related_name='notifications')
    verb = models.CharField(max_length=255, choices=VerbChoices.choices)
    read = models.BooleanField(default=False)
    target_content_type = models.ForeignKey(ContentType, on_delete=models.CASCADE)
    target_object_id = models.CharField(max_length=255)
    target = GenericForeignKey('target_content_type', 'target_object_id')
    created_at = models.DateTimeField(auto_now_add=True)
    last_updated = models.DateTimeField(auto_now=True)

核心需求 #

• 评论类（verb=COMMENTED）通知：同一目标下的多条评论保留独立条目，不做合并
• 点赞类（verb=LIKE）通知：同一目标下的多条点赞合并为单条返回
• 合并后的点赞条目规则：
  • actor取分组内第一条点赞记录的操作用户
  • read字段：分组内只要存在1条未读记录，合并条目就标记为未读
  • 新增count字段，返回该目标下的点赞总数量
  • target保留原关联的目标对象

实现代码 #

from django.db.models import Count, Min, BooleanField, Case, When, Value
from django.contrib.contenttypes.models import ContentType

def get_notification_list(recipient_user):
    # 1. 查询所有非点赞类通知，保留原始单条结构
    comment_qs = Notification.objects.filter(
        recipient=recipient_user
    ).exclude(
        verb=Notification.VerbChoices.LIKE
    ).select_related('actor', 'target_content_type')

    # 2. 点赞类通知按目标对象分组聚合
    like_group_qs = Notification.objects.filter(
        recipient=recipient_user,
        verb=Notification.VerbChoices.LIKE
    ).values('target_content_type_id', 'target_object_id').annotate(
        nid=Min('id'),
        first_actor_id=Min('actor_id'),
        is_read=Case(
            When(read=False, then=Value(False)),
            default=Value(True),
            output_field=BooleanField()
        ),
        like_count=Count('id'),
        latest_time=Min('last_updated')
    )

    # 3. 组装点赞类结构化数据
    like_list = []
    for group in like_group_qs:
        # 取关联目标对象
        ct = ContentType.objects.get_for_id(group['target_content_type_id'])
        target_obj = ct.get_object_for_this_type(pk=group['target_object_id'])
        # 取第一个点赞的用户
        first_actor = Users.objects.filter(pk=group['first_actor_id']).first() if group['first_actor_id'] else None
        
        like_list.append({
            "id": group['nid'],
            "actor": first_actor,
            "recipient": recipient_user,
            "verb": "liked",
            "read": group['is_read'],
            "target": target_obj,
            "count": group['like_count'],
            "last_updated": group['latest_time']
        })

    # 4. 组装评论类数据
    comment_list = []
    for notify in comment_qs:
        comment_list.append({
            "id": notify.id,
            "actor": notify.actor,
            "recipient": notify.recipient,
            "verb": notify.verb,
            "read": notify.read,
            "target": notify.target,
            "last_updated": notify.last_updated
        })

    # 5. 合并两类结果，按更新时间倒序排列
    final_list = comment_list + like_list
    final_list.sort(key=lambda x: x['last_updated'], reverse=True)
    
    return final_list

注意点 #

• 两类通知拆分查询，避免分组逻辑影响非点赞数据的返回结构
• 聚合未读状态时用Case条件判断，不需要查询全组所有记录的read状态，性能更好
• 如果序列化时不需要返回完整的对象实例，可以直接在聚合阶段取出需要的字段id，减少跨表查询次数
• 排序规则可以根据业务调整，比如换成按created_at倒序

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