1 Reply Latest reply on Nov 26, 2013 8:50 AM by Randall Hauch

    Get nodes by child in search query

    Nayyereh Fazileh Newbie

      I want to get some nodes that have special child in the query.

      select * from [nt:unstructured] as emails where path(h) like '/users/admin/send/%/user4'

      but this query gets me all nodes 'user4' in mentioned path.

       

      I want something like this:

      select * from [nt:unstructured] as emails where path(h) like '/users/admin/send/%' and child= 'user4'

       

      Is there something like substring in jcr grammar for queries?

       

      I hope I could get what I mean.

      Thank in advance

        • 1. Re: Get nodes by child in search query
          Randall Hauch Master

          To do this in JCR-SQL2, you have to declare a selector (e.g., table) for both the parent node and the child of that parent, and you have to use a join to constrain that the child node is actually a child of the parent. Now, I'm not sure whether the wildcard in the LIKE clause you used wanted the parent node to be directly under '/users/admin/send' or some descendant under '/users/admin/send'.

           

          A query that only uses the standard grammar that does the latter is:

           

          SELECT parent.* FROM [nt:unstructured] AS parent
            JOIN child ON ISCHILDNODE(child,parent)
           WHERE ISDESCENDANTNODE('/users/admin/send',parent)
             AND NAME(child) = 'user4'
          

           

          Thus a node at '/users/admin/send/a/b/c' might satisfy the criteria as long as there was also a node at '/users/admin/send/a/b/c/user4'.

           

          If, however, you did not want to allow the parent to be anywhere below the '/users/admin' node, then you should use an ISCHILDNODE constraint instead:

           

          SELECT parent.* FROM [nt:unstructured] AS parent
            JOIN child ON ISCHILDNODE(child,parent)
           WHERE ISCHILDNODE('/users/admin/send',parent)
             AND NAME(child) = 'user4'
          

           

          Note these queries are basically the same except on line 3.

           

          BTW, ModeShape's implementation does support a few extra functions, so you could instead do this:

           

          SELECT parent.* FROM [nt:unstructured] AS parent
            JOIN child ON ISCHILDNODE(child,parent)
           WHERE PATH(parent) LIKE '/users/admin/send/%'
             AND NAME(child) = 'user4'
          

           

          The problem with this approach is that its hard to remember/know whether the wildcard will apply just to a single path segment or to all paths. Anyone reading the query has to know the behavior. (Really, this query should be identical to the first query I listed above, since LIKE is not really aware of path segments and simply treats the path as a string.) Because this query is not as explicit, I'd recommend not using it. (The "PATH" function was added because it can be used with other comparison operators, such as <, >, <=, and >=, which can't be done any other way.)

           

          Hope this helps.