How to filter on a join where the referenced item is NOT present

[janvandeklok]

Hello there,

  I'm trying to query my modeshape tree for elements that don't have a specific sub node (caseResult). This is the query I ended up with but it does give the correct results:

Select * from [caci:case] as case

left join [caci:caseResult] as caseResult on ischildnode(caseResult,case)

where

caseResult.[jcr:created] IS NULL

I need to get all case nodes that don't have a caseResult as subnode.

How can I achieve this???

Any help is appreciated

Jan

Using modeshape 3.7.3

[rhauch]

You're query isn't quite right: I think you might have intended to use a LEFT OUTER JOIN.

The problem is that the 3.x query engine cannot easily implement such null checks on joins, which is why MODE-2151 will be fixed only in 4.0.

The workaround is to use a property on the parent to specify whether it has the child you are looking for. In fact, even in other JCR implementations this is the recommended practice, since it turns a two-way join into a non-join criteria.