Hi,

I have a problem with a report query I'm attempting using HQL.

I have a number of clients, each of which has bought a 0 or more textbooks, each of which belongs to a category. I want a report query that joins the client and textbook records, but only returns rows for a specific category (by name). However, I want my query to include rows for clients who have not bought any textbooks (i.e. left outer join required!).

My HQL query is like this...

select c.name, c.address, t.textbook
from Client c
left join c.textbook t
with t.category.name = 'Java'


I specifically want to limit the results of the left join using the with command.

However, it seems I am not allowed to refer to child objects within the with command - the clause with t.category.name = 'Java' causes Hibernate to throw an exception.

If I change the clause to refer to one of the direct attributes of 't' (e.g. 't.author') there is no problem.

Can you recommend the best approach to filter the results of the join, using a related child object as criteria?


Many thanks,
Chris

If its any help, the exception hibernate generates is:

org.hibernate.hql.ast.InvalidWithClauseException: with-clause expressions did not reference from-clause element to which the with-clause was associated

Try this: