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?