3 Replies Latest reply on Jun 5, 2013 12:48 PM by rush_pawan

    Jackrabbit search jcr repository on multiple paths using single query

    cqexperiment Level 1

      Hi,

       

      I have been trying to work this out: using either xpath or SQL or SQL2, to perform a full text search on multiple paths.

       

      i.e.

       

      Search for 'promotion' under '/content/mysite/products/%' and '/content/dam/articles/pdfs/products' etc.

       

      Tried using the following but none worked so far:

       

      SQL2:

       

      SELECT * FROM [nt:base] AS s

      WHERE  ISDESCENDANTNODE([/content/mysite/products]) or                ISDESCENDANTNODE([/content/dam/articles/pdf/products]) 

      and CONTAINS(s.*, 'promotion')

       

      SQL:

      select * from nt:base where (jcr:path like '/content/mysite/products/%' or jcr:path like '/content/dam/articles/pdf/products/%') and contains (., 'promotion')

       

      xpath:

       

      /jcr:root/content/[fn:name()='/mysite/products' or fn:name()='/dam/articles/pdf/products]//*[jcr:like(text, '%promotion%')]

       

      The reason for making a query like this is because we can than sort the result by jcr:score. If separate queries were used on each path then we need to work out how to consolidate and sort everything according to the score.

       

      Already tried predicateGroup but:

      1. Very slow

      2. 'lower' function isn't working on fulltext search

       

      Appreciate any advice or suggestions.