Having used Hibernate before but never to this extent, I have struggled while trying to translate the following query into a valid Hibernate SQL query.
SELECT * FROM
(SELECT * FROM scripts WHERE (script.type LIKE 'DATA'))
AS A JOIN
(SELECT * FROM req_executions
WHERE (req_executions.fk_database NOT IN
(SELECT executions.fk_database FROM executions
WHERE executions.fk_script = req_executions.fk_script))
OR ((req_executions.fk_database = 12 AND req_executions.fk_database != 8)
AND req_execution.fk_database NOT IN (SELECT executions.fk_database FROM execution
WHERE executions.fk_script = req_executions.fk_script)))
AS B
ON (A.id = B.fk_script)
The result of this query is a filtered table of scripts that are pending for execution. I have tried things like:
"SELECT req FROM (SELECT s FROM Script s
WHERE( s.filename LIKE :filename AND s.type = :scriptType AND s.ticketNumber LIKE :ticketNumber )
JOIN (SELECT re FROM RequiredExecution re
WHERE (re.key.databaseId NOT IN (SELECT e.database FROM Execution e WHERE e.script.id = re.key.scriptId))
OR ((re.key.databaseId = :forAllId AND :dbId != :syncId)
AND re.key.databaseId NOT IN
(SELECT e.database FROM Execution e WHERE e.script.id = re.key.scriptId)))) WHERE (s.id = re.key.scriptId)"
and using "AS" but I am still far from a solution. Any tips?
In the end I was forced to use a nativeQuery instead, because "...HQL subqueries can occur only in the select or where clauses." as stated in 14.13. Subqueries.