如何用PostgreSQL筛选指定号码的唯一SMS对话并获取最新时间戳

要实现你需要的功能，核心是按对方号码分组并取每组的最新时间戳，而非简单使用distinct。以下是修正后的TypeORM查询构建器代码及关键逻辑说明：

修正后的查询代码 #

.createQueryBuilder('smse')
// 筛选与当前用户相关的所有消息（发出或接收）
.where('smse.from = :userPhonenumber OR smse.to = :userPhonenumber', { userPhonenumber })
// 统一提取对方号码：用户发出的消息取接收方，收到的消息取发送方
.addSelect(`CASE WHEN smse.from = :userPhonenumber THEN smse.to ELSE smse.from END`, 'contact_number')
// 获取每组的最新活动时间戳
.addSelect('MAX(smse.createdAt)', 'latest_activity')
// 按对方号码分组，确保每个号码仅出现一次
.groupBy('contact_number')
// 按最新活动时间倒序，最近的记录排在前面
.orderBy('latest_activity', 'DESC')
// 限制返回5条记录，满足分页需求
.limit(5)
.getRawMany()

关键逻辑说明 #

• WHERE条件修正：原代码用AND会错误筛选出仅用户给自己发消息的记录，改为OR才能覆盖所有用户发出或接收的消息。
• 分组字段定义：通过CASE表达式统一提取互动的对方号码，确保用户与同一号码的所有收发消息都被归为同一组。
• 聚合函数取最新时间：用MAX(createdAt)获取每组内的最新消息时间戳，作为该号码的最后活动时间。
• 排序与分页：按latest_activity倒序排序后取前5条，完美匹配“最近5条活动记录”的需求。

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