write inner join with sub query in spring data JPA query

Question

I have the below sql query running to have distinct data from table order by date which is working fine.

select * from content_details c
inner join(select spendpool_id, max(uploaded_date) as latest
from content_details group by spendpool_id) r on c.uploaded_date = latest
and r.spendpool_id = c.spendpool_id
where c.spendpool_id IN (2,3,4,5)
group by c.spendpool_id order by uploaded_date desc;

Here is my JPARepository query for the same.

@Query("SELECT m1.spendpoolId FROM ContentDetails m1 join ( "
        + "select spendpoolId, MAX(uploadedDate) latest from ContentDetails "
        + "group by spendpoolId) m2 "
        + "on m1.uploadedDate = latest and m2.spendpoolId= m1.spendpoolId "
        + "where m1.spendpoolId IN (:spendpoolIds) "
        + " group by m1.spendpoolId "
        + "order by m1.uploadedDate desc ) ")
List<Spendpool> findSortedSpendpoolIds(@Param("spendpoolIds") List<Long> spendpoolIds);

But whenever I use sub query using query annotation, spring data on runtime cannot recognize the syntax and throws below error.

Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: ( near , column 85 [SELECT m1.spendpoolId FROM com.beroe.insync2.domain.entities.ContentDetails m1 join ( select spendpoolId, MAX(up Date) latest from com.beroe.insync2.domain.entities.ContentDetails group by spendpoolId) m2 on m1.uploadedDate = latest and m dpoolId= m1.spendpoolId where m1.spendpoolId IN (:spendpoolIds) group by m1.spendpoolId order by m1.uploadedDate desc ) ]

How can I make this work in jpa ?

Answer 1

You can use native SQL query instead of jpa based because while converting to jpa object to SQL query, its not understand '(' , here expecting Object