如何将复杂的SQL查询转换为JPQL

Question

我在SQL中有以下查询。

这里的发件人文档表包含发件人和文档实体。

@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
}

我必须找到截止日期介于开始和结束之间的所有发件人的最新文档。

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'

我必须将其转换为JPQL或使用标准API。我使用的是Postgres数据库。

如何将其转换为单个JPQL？

Answer 1

我刚刚意识到用JPQL编写复杂查询有多难。

我发现了如下的解决方案。

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