3 Replies Latest reply on Jan 29, 2014 11:36 AM by Randall Hauch

    Checking if child nodes exists with jcr-sql2

    Xander Den Hartog Newbie

      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

        • 1. Re: Checking if child nodes exists with jcr-sql2
          Randall Hauch Master

          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 Den Hartog Newbie

            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

            • 3. Re: Checking if child nodes exists with jcr-sql2
              Randall Hauch Master

              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.

              1 of 1 people found this helpful