How to convert complex SQL query to JPQL?

Question

I have the following query in SQL.

Here sender document table contains the sender and document entity.

@Entity
@Table(name = "sender_document")
public class SenderDocument  {

    @Id
    @Column(columnDefinition = "uuid")
    protected UUID id;

    @Column(name = "created_at")
    private LocalDateTime createdAt;

    @ManyToOne
    @JoinColumn(name = "sender_id")
    private Sender sender;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "document_id")
    private Document document;
    
    //other columns

}

I have to find the latest document of all sender whose expiry date is between start and end.

select * from sender_document join
(select sender_id, max(created_at) as maxDate
from sender_document
group by sender_id ) as t2
on created_at = t2.maxDate
and t2.sender_id = sender_document.sender_id
where sender_document.document.expiry_date between 'start_date' and 'end_date'

I have to convert this to JPQL or using criteria API. I am using the Postgres database.

How to convert this to single JPQL?

Do you have an entity that represent your `sender_document` table? If so, please show it. — SternK, Sep 11 '20 at 10:34
I tried a similar question's solution from [https://stackoverflow.com/a/20707219/10147096] but it is not working for me. — Shankar Ghimire, Sep 11 '20 at 11:16
@Shankar Ghimire, I would definitely suggest to stick to the query mentioned in the SO link you have provided. Are you able to construct the query like that ? or what is the issue ? — Sujitmohanty30, Sep 11 '20 at 12:11

score 0 · Answer 1 · answered Nov 08 '20 at 12:21

I just realize how hard it is to write complex queries in JPQL.

I came across the solution something like below.

SELECT sd FROM SenderDocument sd 
where sd.createdAt = (SELECT MAX(s.createdAt) FROM SenderDocument  s 
where s.sender = sd.sender and s.document.type in :types 
and sd.document.type in :types) 
and (:mtoId is null or sd.sender.mtoId = :mtoId) and sd.document.expiryDate 
between :start and :end order by sd.createdAt asc

How to convert complex SQL query to JPQL?

1 Answers1