SQL inner join with top to JPPQL

Question

I have this SQL:

SELECT b.NAME, b.ID, hc1.CATCH_DATE, hc1.id
FROM BIRD b
         INNER JOIN HEALTH_CHECK hc1 ON hc1.BIRD_ID = b.ID
         INNER JOIN
     (
         SELECT BIRD_ID, MAX(CATCH_DATE) AS MAX_DATE
         FROM HEALTH_CHECK
         GROUP BY BIRD_ID
     ) hc2
     ON hc2.BIRD_ID = hc1.BIRD_ID AND
        hc2.MAX_DATE = hc1.CATCH_DATE
WHERE b.ID=1;

This returns a bird record with only the newest HealthCheck.

I would like to convert this to JPQL but can't find how to do this with the nested INNER JOIN.

Answer 1

You must use SQL because JPQL is not able to create such a query.

As a result, you must use a DTO. The result of the query can be transformed to DTOs using Hibernate Transformer

For example:

List<PersonSummaryDTO> dtos = session.createNativeQuery(
    "SELECT p.id as \"id\", p.name as \"name\" " +
    "FROM Person p")
.setResultTransformer( Transformers.aliasToBean( PersonSummaryDTO.class ) )
.list();

Please find all information in the Hibernate documentation: https://docs.jboss.org/hibernate/orm/current/userguide/html_single/Hibernate_User_Guide.html#sql-dto-query