Spring Data JPA实现按ULTIMATE_PARENT_ID去重分页查询最新消息

场景与表结构 #

现有用户消息表结构如下：

需求 #

• 拉取指定收件人的最新消息
• 同一个ULTIMATE_PARENT_ID只保留一条最新消息（按该字段去重）
• 支持Spring Data JPA的Pageable分页

预期结果：

踩过的坑：无效查询 #

第一种逻辑错误的查询 #

@Query(value = "SELECT dm FROM " +
        "(SELECT DISTINCT d.ultimateParentId from DirectMessage d WHERE dm.recipient.id = :recipientId) DirectMessage dm " +
        "ORDER BY dm.sentAt DESC", nativeQuery = true)
Page<DirectMessage> findPaginatedDirectMessagesByRecipientIdGroupByUltimateParentId(Long recipientId, Pageable pg);

这个查询逻辑混乱：子查询只提取了ultimateParentId，主查询却试图关联完整的DirectMessage对象，根本无法获取有效消息数据。

第二种语法错误的查询 #

@Query(value = "SELECT * FROM (SELECT DISTINCT ON (ultimate_parent_id) * FROM direct_message WHERE recipient_id = :recipientId ORDER BY ultimate_parent_id DESC) direct_message ORDER BY sent_at DESC", nativeQuery = true)

触发PostgreSQL语法错误：

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "WHERE"

问题出在PostgreSQL的DISTINCT ON规则：子查询的ORDER BY必须先按DISTINCT ON指定的字段排序，同时要加上sent_at DESC来明确“最新”的判断逻辑，否则数据库无法确定分组内的筛选规则，直接报错。

有效解决方案 #

最终通过原生SQL实现需求，同时支持分页：

@Query(value = "SELECT outerDM.* FROM direct_message outerDM " +
        "INNER JOIN (SELECT ultimate_parent_id, MAX(sent_at) AS last_sent FROM direct_message " +
        "WHERE recipient_id = :recipientId GROUP BY ultimate_parent_id) innerDM " +
        "ON outerDM.ultimate_parent_id = innerDM.ultimate_parent_id AND outerDM.sent_at = innerDM.last_sent", nativeQuery = true)
Page<DirectMessage> findPaginatedDirectMessagesByRecipientIdGroupByUltimateParentId(Long recipientId, Pageable pg);

逻辑说明 #

1. 子查询innerDM：筛选指定收件人的所有消息，按ultimate_parent_id分组，计算每个分组的最新发送时间last_sent
2. 主查询通过INNER JOIN关联原表，匹配分组ID和最新发送时间，精准获取每个分组的最新消息
3. Spring Data JPA会自动将Pageable参数转换为对应的LIMIT和OFFSET语句，原生支持分页功能

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