Checking if child nodes exists with jcr-sql2

[xander.denhartog]

Hi,

I'm trying to make a jcr-sql2 query that checks if a node has a specific child node. And then select all nodes that don't have that specific child node.

Thanks

Xander

[rhauch]

Please provide more information, including a brief summary of the node structure and the queries you've tried with the expected and actual behavior/output.

[xander.denhartog]

We got a structure like this:
root/engine/case
case has child nodes wich numbers each of the primarytype [caci:case] case I.E.

case/1

case/2
case/3

some of these cases have ended and have a node under them named caseResult primarType [caci:caseResult] like this:
case/1/caseResult
case/2
case/3/caseResult

First i tried this query:

SELECT case.number, cr.result

FROM [caci:case] as case

LEFT OUTER JOIN [caci:caseResult] as cr

ON ISCHILDNODE(cr,case)

and it gave back:

1          passed

2          null

3          rejected

I only want the cases without the caseResult node so I tried this:

SELECT case.number, cr.result

FROM [caci:case] as case

LEFT OUTER JOIN [caci:caseResult] as cr

ON ISCHILDNODE(cr,case)

WHERE cr.[jcr:createdBy] IS NULL

I expected this result:
2          null

This was the actual result:
1          null

2          null

3          null

[rhauch]

ModeShape does not appear to properly handle returning only the rows in the LEFT OUTER JOIN for which there is a NULL value on the right. (The LEFT INNER JOIN will return only the rows for which there is a non-null value on the right.) Feel free to log an issue and we'll get it fixed for 4.0.

A workaround is simply to find all of the cases and then when processing the result set get the case node and skip it if it has the result child node. It's not ideal, but it will work.

An even better workaround that will be very efficient is to set a property (e.g., 'caci:resolved' of type BOOLEAN) on the case node when it is resolved. That query would not use a join at all.