JPA查询用户最新私信去重问题：排除已在消息线程的父消息

问题 #

我有一张名为direct_message的表，用于存储用户间的私信数据，表结构如下：

我希望返回按SENT_AT排序的最新消息列表，目前使用JPA实现，通过接收者ID查询的代码如下：

@Repository
@Transactional(readOnly = true)
public interface DirectMessageRepository extends JpaRepository<DirectMessage, Long> {
    Page<DirectMessage> findDirectMessageByRecipientId(Long recipientId, Pageable pageable);
}

对应的Entity类代码如下：

@Getter
@Setter
@NoArgsConstructor
@Entity
@Table(name = "direct_message")
public class DirectMessage {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "direct_message_sequence")
    @SequenceGenerator(name = "direct_message_sequence", sequenceName = "direct_message_sequence", allocationSize = 1)
    @Column(name = "id", nullable = false)
    private Long id;

    @ManyToOne
    @JoinColumn(name = "parent_id")
    private DirectMessage parent;

    @ManyToOne
    @JoinColumn(nullable = false, name = "sender_id")
    private User sender;

    @ManyToOne
    @JoinColumn(nullable = false, name = "recipient_id")
    private User recipient;

    private String title;

    @Lob
    @Column(columnDefinition = "TEXT")
    @Size(max = 4000)
    private String message;

    @Column(name = "sent_at")
    private LocalDateTime sentAt;

    private Boolean isRead = false;

    @Column(name = "read_at")
    private LocalDateTime readAt;

    @Column(name = "can_reply")
    private Boolean canReply;
}

但查询结果存在问题：父消息既作为独立条目返回，又作为子消息的嵌套父对象重复出现，简化示例如下（省略发送者、接收者等数据）：

[{
    "id": 1,
    "parent": null,
    "recipient": {},
    "sender": {},
    "title": "Message thread",
    "message": "Hello"
}, {
    "id": 2,
    "parent": {
        "id": 1,
        "parent": null,
        "recipient": {},
        "sender": {},
        "title": "Message thread",
        "message": "Hello"
    },
    "recipient": {},
    "sender": {},
    "title": "Message thread",
    "message": "This is the reply"
}]

如示例所示，ID为1的父消息重复出现（既是独立行，又是子消息的父对象），需要实现去重，仅返回未属于其他消息线程的消息。

解决方案 #

一、JPA实现方案 #

1. 数据库层面过滤顶级消息

要只返回没有被其他消息作为父级的顶级消息，可以在Repository中定义自定义查询：

@Repository
@Transactional(readOnly = true)
public interface DirectMessageRepository extends JpaRepository<DirectMessage, Long> {
    // 用NOT EXISTS判断当前消息不是任何子消息的父级
    @Query("SELECT dm FROM DirectMessage dm WHERE dm.recipient.id = :recipientId AND NOT EXISTS (SELECT child FROM DirectMessage child WHERE child.parent.id = dm.id) ORDER BY dm.sentAt DESC")
    Page<DirectMessage> findTopLevelMessagesByRecipientId(Long recipientId, Pageable pageable);
}

这个查询会直接从数据库中过滤掉那些被其他消息引用为父级的消息，返回的结果中只有顶级消息，自然避免了重复。

2. 控制序列化避免嵌套重复

如果你的需求是保留子消息，但不想在子消息中序列化父对象（避免重复输出），可以在实体的parent字段添加Jackson注解：

@ManyToOne
@JoinColumn(name = "parent_id")
@JsonIgnore // 序列化时忽略父对象
private DirectMessage parent;

或者用双向关联的序列化注解避免循环和重复：

// 在实体中添加子集合（如果需要维护双向关联）
@OneToMany(mappedBy = "parent")
@JsonManagedReference
private List<DirectMessage> children;

// parent字段使用@JsonBackReference
@ManyToOne
@JoinColumn(name = "parent_id")
@JsonBackReference
private DirectMessage parent;

这种方式不会改变数据库查询结果，只是在返回JSON时隐藏父对象，适合需要保留子消息但不想看到重复父内容的场景。

二、SQL查询方案 #

直接用SQL实现的话，核心逻辑和JPA的NOT EXISTS查询一致：

SELECT dm.*
FROM direct_message dm
WHERE dm.recipient_id = ?
AND NOT EXISTS (
    SELECT 1 FROM direct_message child
    WHERE child.parent_id = dm.id
)
ORDER BY dm.sent_at DESC
LIMIT ? OFFSET ?; -- 对应分页的每页数量和偏移量

如果要兼容parent_id为NULL的情况，也可以用NOT IN写法（注意排除NULL）：

SELECT dm.*
FROM direct_message dm
WHERE dm.recipient_id = ?
AND dm.id NOT IN (
    SELECT parent_id FROM direct_message
    WHERE parent_id IS NOT NULL
)
ORDER BY dm.sent_at DESC
LIMIT ? OFFSET ?;

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