Imagine a table of user messages that looks like this:
| ID | MESSAGE | RECIPIENT_ID | ULTIMATE_PARENT_ID | SENT_AT |
| 1 | Blah. | 10 | 1 | 2024-09-10T10:10:00 |
| 2 | Blah2 | 10 | 2 | 2024-09-11T12:20:00 |
| 3 | Blah3 | 10 | 1 | 2024-09-12T15:10:00 |
| 4 | Blah4 | 10 | 1 | 2024-09-13T16:10:00 |
What I would like to send back is the latest message to a user, with the ULTIMATE_PARENT_ID deduplicated. i.e. Each row will have a unique ultimate parent id. I will be sending a pagination object (Pageable) which will hopefully take care of the pagination for me.
Basically the query on the above table should send back:
| ID | MESSAGE | RECIPIENT_ID | ULTIMATE_PARENT_ID | SENT_AT |
| 2 | Blah2 | 10 | 2 | 2024-09-11T12:20:00 |
| 4 | Blah4 | 10 | 1 | 2024-09-13T16:10:00 |
I am using Spring and Spring Data JPA. But if it has to be done using native SQL that is fine. What I have tried, but doesn’t work is:
@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);
Any help on this is greatly appreciated because I can’t find a solution, either in my own head or online!
You need to sign in to view this answers
Leave feedback about this