-
1. Re: Checking if child nodes exists with jcr-sql2
rhauch Jan 29, 2014 10:15 AM (in response to xander.denhartog)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.
-
2. Re: Checking if child nodes exists with jcr-sql2
xander.denhartog Jan 29, 2014 10:40 AM (in response to rhauch)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/3some 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/caseResultFirst 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 nullThis was the actual result:
1 null2 null
3 null
-
3. Re: Checking if child nodes exists with jcr-sql2
rhauch Jan 29, 2014 11:36 AM (in response to xander.denhartog)1 of 1 people found this helpfulModeShape 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.