2024年11月4日 12:59 by wst
web开发虽然做python开发很多年了，对于一些自己不熟悉的细节（比如Django的orm查询），仍然掌握的不太好。但是呢，现在打算使用rest_framework框架开发接口，它牵涉到很多的查询，不想老是写sql去查询数据了。
现在有个聊天室列表，当某个用户登录的时候，要显示聊天室（这里为私聊）列表，以及当前聊天室有几条未读消息；
借助ChatGPT去帮我做一些基础的工作，其实也不算是基础工作，而是直接把问题抛给它了，结果直接粘贴下来的代码跑不通。这里截取一个和ChatGPT聊天的记录：
--------------------------------开始----------------------------------
问：请根据我的思路（先查询出包含目标room的记录，然后根据room分组，分组后取created_at最新的那条记录）实现需求。
答：
根据您的条件，我们需要找到包含特定用户（假设用户ID为1）的聊天室的最新记录。这意味着我们需要查找Chats表中，sender或accepter字段为该用户ID的记录，并为每个聊天室（room）找到最新的记录。
以下是实现这一需求的Django ORM查询代码：
from django.db.models import Max, Window, RowNumber
# 目标用户ID
target_user_id = 1
# 找到包含目标用户的聊天室的最新记录
latest_chats = Chats.objects.annotate(
rank=Window(
expression=RowNumber(),
partition_by=['room'],
order_by=['-created_at']
)
).filter(
Q(sender_id=target_user_id) | Q(accepter_id=target_user_id)
).filter(
rank=1
).values(
'room', 'content', 'created_at', 'sender', 'accepter'
)
# 输出查询结果
for chat in latest_chats:
print(chat)
在这个代码中，我们使用了窗口函数RowNumber()来为每个room分组内的记录分配一个排名，然后根据created_at字段降序排序，这样每个分组中created_at最新的记录将被分配排名1。然后我们通过filter(rank=1)过滤出这些记录。
我们还使用了Q对象来构建查询条件，以便找到sender或accepter字段为特定用户ID的记录。
请注意，窗口函数需要数据库支持窗口函数功能，比如PostgreSQL。如果您使用的数据库不支持窗口函数，那么您可能需要使用子查询或者其他方法来实现相同的功能。
如果您的数据库不支持窗口函数，以下是一个使用子查询的替代方案：
from django.db.models import Max
# 首先，找到每个包含目标用户的聊天室的最新created_at时间
latest_times = Chats.objects.filter(
Q(sender_id=target_user_id) | Q(accepter_id=target_user_id)
).values('room').annotate(
latest_created_at=Max('created_at')
)
# 然后，根据最新的created_at时间查询每个聊天室的最新记录
latest_chats = []
for latest_time in latest_times:
latest_record = Chats.objects.filter(
room=latest_time['room'],
created_at=latest_time['latest_created_at']
).values('room', 'content', 'created_at', 'sender', 'accepter').first()
if latest_record:
latest_chats.append(latest_record)
# 输出查询结果
for chat in latest_chats:
print(chat)
在这个替代方案中，我们首先找到每个包含目标用户的聊天室的最新created_at时间，然后根据这些时间查询每个聊天室的最新记录。这种方法不需要窗口函数，但是可能会有更多的数据库查询，特别是当聊天室的数量很多时。
---------------------------------结束---------------------------------------
ChatGPT的回答看似完美，其实不然。
此时还要结合自己的已有知识，比如这个sql怎么实现我是知道的：
SELECT * from ylbw_chats t WHERE (t.room,t.id) in (SELECT room, max(id) as id from ylbw_chats GROUP BY room);
然后就开始让ChatGPT帮我翻译成orm查询。记录如下：
---------------------------------开始--------------------------------------
问：SELECT * from ylbw_chats t WHERE (t.room,t.id) in (SELECT room, max(id) as id from ylbw_chats GROUP BY room);能把这个查询改为orm查询吗？
答：
是的，可以将这个SQL查询转换为Django ORM查询。这个SQL查询的目的是选择每个room中id最大的记录，这可以通过使用窗口函数来实现。以下是对应的Django ORM查询：
from django.db.models import Window, RowNumber
# 使用窗口函数RowNumber进行分区排序，并选择每个分区（room）中id最大的记录
latest_chats = Chats.objects.annotate(
rank=Window(
expression=RowNumber(),
partition_by=['room'],
order_by=['-id'] # 假设id是自动增长的主键，我们按照id降序排列来获取最新的记录
)
).filter(rank=1).values('room', 'content', 'created_at', 'sender', 'accepter', 'id')
# 输出查询结果
for chat in latest_chats:
print(chat)
在这个ORM查询中，我们使用了Window和RowNumber来为每个room内的记录分配一个排名，然后按照id降序排列。rank=1的记录就是每个room中id最大的记录，即最新的记录。
请注意，这个查询假设id字段是Chats模型的主键，并且是自动增长的。如果id不是主键或者不是自动增长的，请根据实际情况调整order_by中的字段。
此外，如果您的数据库不支持窗口函数，那么您需要使用子查询来实现相同的功能。以下是使用子查询的替代方案：
from django.db.models import Max
# 首先，找到每个room中id最大的记录的id
max_ids = Chats.objects.values('room').annotate(max_id=Max('id'))
# 然后，根据这些最大的id查询对应的记录
latest_chats = Chats.objects.filter(id__in=[chat['max_id'] for chat in max_ids])
# 输出查询结果
for chat in latest_chats:
print(chat)
在这个替代方案中，我们首先找到每个room中id最大的记录的id，然后根据这些id查询对应的记录。这种方法不需要窗口函数，但是可能会有更多的数据库查询，特别是当room的数量很多时。
--------------------------------------结束-------------------------------------
from rest_framework import serializers
from .models import Chats, UserProfile
from django.db.models import F, Max
from django.db.models.functions import Coalesce
def get_chat_rooms_with_details(user):
"每个聊天室的最新消息时间和未读消息数量"
# 首先，找到每个room中id最大的记录的id
max_ids = Chats.objects.filter(accepter=user).values('room').annotate(max_id=Max('id'))
# 然后，根据这些最大的id查询对应的记录
chat_rooms = Chats.objects.filter(id__in=[chat['max_id'] for chat in max_ids], ).values(
'id','content', 'room', 'sender__id', 'sender__avatar','created_at').annotate(
unread_count=Coalesce(Chats.objects.filter(
room=F('room'), accepter=user, status='0').count(),0)
)
return chat_rooms
class CompositeRoomSerializer(serializers.Serializer):
room = serializers.CharField(read_only=True) # 假设room字段是字符串类型
latest_message = serializers.CharField(read_only=True, source='content')
sender_id = serializers.IntegerField(read_only=True, source='sender__id')
sender_avatar = serializers.ImageField(read_only=True, source='sender__avatar')
created_at = serializers.DateTimeField(read_only=True)
unread_count = serializers.IntegerField(read_only=True)
我们采用的chatGpt为Kimi，聊天室的数据模型为：
class Chats(models.Model):
"聊天表"
CTYPE_CHOICES = (
(0,'申请'),
(1, '消息'),
(2, '其他'),
)
STATUS_CHOICES = (
(0,'未读'),
(1, '已读'),
(2, '其他'),
)
sender = models.ForeignKey(UserProfile, on_delete=models.SET_DEFAULT, default=0, verbose_name='发送者', related_name='sender')
accepter = models.ForeignKey(UserProfile, on_delete=models.SET_DEFAULT, default=0, verbose_name='接收者', related_name='accepter')
room = models.CharField(max_length=16, verbose_name='聊天室编号', help_text='聊天室编号')
content = models.CharField(max_length=128, verbose_name='聊天内容', help_text='聊天内容')
created_at = models.DateTimeField(auto_now_add=True, verbose_name='创建时间')
updated_at = models.DateTimeField(auto_now=True, verbose_name='更新时间')
c_type = models.IntegerField(default=0, choices=CTYPE_CHOICES, verbose_name='消息类型') # 假设0为申请创建聊天室
status = models.IntegerField(default=0, choices=STATUS_CHOICES, verbose_name='消息状态') # 假设0为申请创建聊天室
def __str__(self):
return self.content
本次完整的聊天记录在这里：点击链接查看和 Kimi 智能助手的对话 https://kimi.moonshot.cn/share/csk576kimlneknbbp7sg
现在All in AI的时代，ChatGPT还是能帮我们做一些事情的，比如常见的基础的代码。虽然自己也能写，但是毕竟也需要时间啊。
所以借助AI是可以提高我们工作效率的！
如果问题，欢迎留言！
一个爱生活，爱编程的小鲤鱼！