Search code examples
mysqlnhibernatehql

Using subquery in FROM for HQL

I have following SQL that is doing what I want (simplified):

SELECT tcd.Name, MAX(if(tcd.Name = 'Some Name', tcr.Value, NULL)) as 'Value'
FROM TestCaseData tcd, TestCaseDataResult tcr, PVRTable pvr,
(select MAX(sel.PK) as PK from TestCaseDataResult sel GROUP BY sel.PVR_FK, sel.TCD_FK) selector
WHERE tcr.PK = selector.PK AND tcr.TCD_FK = tcd.PK AND tcr.PVR_PK = pvr.PK
GROUP BY pvr.PK
ORDER BY pvr.PK

Selector table is needed because the set can have several records with the same sel.PVR_FK, sel.TCD_FK, and in that case the MAX(...) expression will select the bigger value, but I need to have the value with bigger PK instead (last value)

I want to have HQL instead of SQL. I'm trying to avoid using raw SQL in the code for the obvious reasons.

Looks like HQL does not support subqueries in the FROM. I feel like I can rewrite this so it could work without subquery but with self-join, but I'm struggling to find a correct solution.

I am also quite worried about the performance, so I do not want to have subqueries in SELECT due to inefficiency.

Solution

  • I believe u can try this

    SELECT tcd.Name, MAX(if(tcd.Name = 'Some Name', tcr.Value, NULL)) as 'Value')
FROM TestCaseData tcd, TestCaseDataResult tcr, PVRTable pvr
WHERE 
    tcr.PK IN(select MAX(PK) from TestCaseDataResult GROUP BY PVR_FK, TCD_FK) 
    AND tcr.TCD_FK = tcd.PK 
    AND tcr.PVR_PK = pvr.PK
GROUP BY pvr.PK
ORDER BY pvr.PK

    http://docs.jboss.org/hibernate/core/3.3/reference/en/html/queryhql.html#queryhql-subqueries