Django聊天室模型：获取用户所属所有房间的最新消息

一、SQLite查询解决方案 #

你原有的查询能获取所有房间的最新消息，但缺少筛选指定用户所属房间的条件。可以通过以下两种方式修改：

方式1：使用EXISTS筛选用户所属房间 #

SELECT cm.*, ma.*, cmb.*
FROM chat_message AS cm
INNER JOIN (
    SELECT room_id, MAX(id) AS lm 
    FROM chat_message 
    GROUP BY room_id
) AS last_messages ON last_messages.lm = cm.id
INNER JOIN main_advertisement ma ON cm.advertisement_id = ma.id
INNER JOIN chat_membership AS cmb ON cm.room_id = cmb.id
-- 判断该房间是否存在目标用户的消息（即用户属于该房间）
WHERE EXISTS (
    SELECT 1 
    FROM chat_message AS user_msg 
    WHERE user_msg.room_id = cmb.id 
      AND user_msg.user_id = 3
)
ORDER BY cm.created_at DESC;

方式2：使用DISTINCT ON直接取每个房间最新消息 #

SQLite支持DISTINCT ON语法，可以按房间分组后取最新的消息记录：

SELECT DISTINCT ON (cm.room_id) 
       cm.id, cm.message, cm.created_at, cm.user_id,
       ma.*, cmb.room_name
FROM chat_message AS cm
INNER JOIN main_advertisement ma ON cm.advertisement_id = ma.id
INNER JOIN chat_membership AS cmb ON cm.room_id = cmb.id
-- 关联目标用户的消息记录，筛选出用户所属的房间
INNER JOIN chat_message AS user_msg ON user_msg.room_id = cmb.id AND user_msg.user_id = 3
-- 按房间分组，再按消息创建时间倒序，确保每个房间第一条是最新消息
ORDER BY cm.room_id, cm.created_at DESC;

二、Django ORM实现提示 #

方法1：从Membership模型出发，关联最新消息 #

from django.db.models import Subquery, OuterRef, Max, F
from django.db.models.query import Prefetch
from .models import Message, Membership, CustomUser

# 获取目标用户
target_user = CustomUser.objects.get(id=3)

# 子查询：获取每个房间的最新消息ID
latest_msg_subquery = Message.objects.filter(
    room=OuterRef('pk')
).values('room').annotate(latest_id=Max('id')).values('latest_id')

# 筛选用户所属房间，同时关联最新消息
rooms_with_latest_msg = Membership.objects.filter(
    # 通过Message表关联，筛选用户参与的房间
    message__user=target_user
).annotate(
    latest_message_id=Subquery(latest_msg_subquery)
).prefetch_related(
    # 预取最新消息及其关联的广告、用户，避免N+1查询
    Prefetch(
        'message_set',
        queryset=Message.objects.filter(id=F('latest_message_id')).select_related('advertisement', 'user'),
        to_attr='latest_message'
    )
).distinct()  # 去重，确保每个房间只返回一条记录

# 遍历结果示例
for room in rooms_with_latest_msg:
    print(f"房间名称：{room.room_name}")
    if room.latest_message:
        latest_msg = room.latest_message[0]
        print(f"最新消息内容：{latest_msg.message}")
        print(f"发送者：{latest_msg.user.username}")
        print(f"发送时间：{latest_msg.created_at}")

方法2：直接从Message模型筛选最新消息 #

from django.db.models import Subquery, OuterRef
from .models import Message, CustomUser

target_user = CustomUser.objects.get(id=3)

# 子查询：获取每个房间的最新消息
latest_msg_subquery = Message.objects.filter(
    room=OuterRef('room')
).order_by('-created_at')[:1]

# 筛选用户所属房间的最新消息，去重后每个房间一条记录
user_latest_msgs = Message.objects.filter(
    room__message__user=target_user
).annotate(
    latest_msg_content=Subquery(latest_msg_subquery.values('message')),
    latest_msg_sender=Subquery(latest_msg_subquery.values('user__username')),
    latest_msg_time=Subquery(latest_msg_subquery.values('created_at')),
    room_name=Subquery(latest_msg_subquery.values('room__room_name'))
).values(
    'room_name', 'latest_msg_content', 'latest_msg_sender', 'latest_msg_time'
).distinct('room__id')

# 遍历结果示例
for item in user_latest_msgs:
    print(f"房间名称：{item['room_name']}")
    print(f"最新消息：{item['latest_msg_content']}")
    print(f"发送者：{item['latest_msg_sender']}")
    print(f"时间：{item['latest_msg_time']}")

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