1 Reply Latest reply on Jan 28, 2013 6:37 PM by rush_pawan

    How to write a subquery using JCR SQL2 in CQ5(without ModeShape)

    Rajesh Pandian Level 1

      Hi,

       

      We have a parent node with several levels of child nodes. We are trying to do a node level search i.e., if the search key is present in any of the properties in the parent node or the child nodes, the query must return the parent node alone(NOT the child nodes).

       

      The below JCR SQL2 query will do the above said functionality,

      SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/DB/ParentNode]) and CONTAINS(s.*, 'searchKey')

       

      The constraint is that we have other filters, which when applied, will give only the nodes which has the required property. For example

      SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/DB/ParentNode]) and CONTAINS(s.*, 'searchKey') and column1 = 'filter1'

       

      where column1 is present only in the ParentNode and not the child nodes.

       

      Hence the above query will limit the search to the ParentNode only and it will not search the child nodes. Also when you use the first query you are getting child node path but my requirement is to get all the Parent Node paths.

       

      And so I decided to write a sub-query like the one shown below,

      SELECT * FROM [nt:base] as a

      WHERE PATH() IN (

         SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/DB/ParentNode]) and CONTAINS(s.*, 'searchKey')

      )

      and column1 = 'filter1'

       

      But the above query is not working in CRXDE Lite. When i searched the web it is said that sub-query and join conditions are possible only using ModeShape.

       

      I have no idea what ModeShape is. If any one has any idea on writing a sub query using JCR SQL2 in CQ5 without ModeShape, please help. Thanks in advance.