JPQL sort by property on unrelated entity from Repo

Question

I have a query in mind that requires pagination and I want to build it with Spring JPA. I have 3 tables, detail, header, and claim. A header record can have many (children) detail records and has a OneToMany mapping. A claim has no relationship to header but both have this field claim_num (not the primary key) that is the same value though there are no formal constraints. Claim also has a field 'lastName' I would like to sort by. In a detail repo extending Spring's JPARespository, I had built a method with @Query with the following:

        @Query("select new MyDTO(d, h, c) from Detail d, Header h 
    left join Claim c on h.claimNum = c.claimNum")
   Page<MyDTO> getSomeDetails( new Page(0, 10, new Sort("claim.lastName" ) ))

The goal is to get pages of details and values from header and claim need to be returned in the result set as well. The order by is my current impediment. I am getting 'claim is not a property of detail'. That makes sense but detail formally relates to header but to claim nothing is mapped. How can the generated SQL get the 'order by' correct when passing in Page(Sort()) at the detail level? Thanks for reading and any help is appreciated. This is spring boot but upgraded to hibernate-core 5.1.

Answer 1

The above I discovered had some major problems to it. Particularly for row count. I ended up overhauling this a lot and have something pretty ugly. I had originally followed an example here as I was joining unrelated entities and thought I had something working but the row counts were completely wrong. The test database I was using only had like a max of 150 rows but the row count was something like 47,000 so the result set was ballooning. I added more joins and hard coded the order by and ended up with:

select new AdjusterSortDTO(detail, rfsth, rule, cm ) 
From Detail detail 
left join Rule rule on rule.Detail.DetailId=detail.DetailId 
left join Header  rfsth on rfsth.headerId=detail.header.headerId
left join ClaimMaster  cm 
on rfsth.claimNum = cm.claimNum order by cm.adjusterEmail

At this point not sure if this question will be of value to anyone else.