Problem with JPA query to return the latest direct messages to a user, where each message may have a parent message

I have a table called direct_message which stores a list of direct messages between users which looks like this:

| ID | PARENT_ID | RECIPIENT_ID | SENDER_ID | TITLE | MESSAGE | SENT_AT | READ_AT


I would like to send back a list of the most recent messages ordered by SENT_AT. I am using JPA so the query will send back the list of messages based on RECIPIENT_ID like so:

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

However, my problem is that it will send back a message that is a parent alongside a child message which contains it as a nested parent. Here is a reduced example (with sender, recipient and other data omitted):

[{
    "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"
}]

So as you can see, the parent message (ID 1) is duplicated (as its own row, AND also as a parent of another message which is a child of that message.
Is there a way in JPA to deduplicate this? And only send back a message if it is not already part of another message thread? Or if not possible in JPA is there a SQL query that would do the job?

  • It would help if we could see your DirectMessage entity. And I guess there is a framework doing some “magic” behind the scenes, so please include that in the tags (Spring, probably).

    – 

  • Are parent and child the same entity? And, can’t you just exclude the parent entity in the query result? Btw, why is the above result causing issue in your case?

    – 




Leave a Comment