Power of HQL - Condition on a collection
I have this piece of domain model.
The LinkResult class has a property called Link that point to a AnalyzedLink class that in turn has a collection of AnalyzedLinkExtClass called ExtData. All the relation are unidirectional, this means that there is nothing that bring me from AnalyzedLink to a linkResult and nothing that bring me from AnalyzedLinkExtData to the AnalyzedLink. This is the typical situation where doing complex query can be tricky
Now I had to find all the LinkResult objects that have Status = LinkResultStatus.Ok and the associated AnalyzedLink must not contain an AnalyzedLinkExtData with the property Source equal to a certain value.
The query in HQL is
This is really powerful, I can simply start the query from the LinkResult object, then join to the L.Link and set a condition on L.Status property.
Now I add a not exists with the condition (from AL.ExtData ED where ED.Source = :source) as you can see I do not need to specify any join, but I can select directly from the ExtData Collection Property of the AnalyzedLink result. The SQL generated is
The real magic is done in the subquery, because you can verify that the condition on subquery contains two condition, the first is the link with the outer query, and the second one is the one we put on HQL. But you can surely appreciate the fact that in HQL the query is really clearer and shorter :)