I have a query that I found needs an additional condition in join clause. This is currently not possible with hibernate.
select dr.id, dr.dueDate, c.name, sd.name, d.id, d.name
from Survey s right outer join s.dueReport dr *** with s.merchandiser = ? ***
join dr.surveyDefinition sd join sd.contractor c,
join m.tasks t join t.depot d
where s is null
and t.surveyDefinition = sd
and m = ?
Briefly saying the query searches for all surveys that have not been done yet by particular user. There might be another same surveys made by other users so I need to introduce addional condition in right outer join.
I might also use subselect - the problem is my database is MySQL and this feature is also not available.
Without the solution I will end up with making one master query and thousands details query (list all "tasks" and check if they have been done by particular user by querying survey existence). The number of queries will also grow in time making the function call last longer and longer.