1 Reply Latest reply on Jun 25, 2013 5:48 PM by rush_pawan

    SQL2 | How to add condition on multi-value field?

    KosPol

      Hello,

       

      I am trying to perform a JCR query using SQL2 in CQ5.6. For one of the conditions, I wish to compare a multi-value field (E.g. cq:tags).

       

      How would that be possible? Currenlt, I only managed to compare my query item's cq:tags to a single value (e.g. WHERE page.[cq:tags]='mynspace:mytag').

       

      Also, is there a syntax reference for SQL2 queries? Currently I have only managed to find something tanglible here : http://svn.apache.org/viewvc/jackrabbit/trunk/jackrabbit-spi-commons/src/test/resources/or g/apache/jackrabbit/spi/commons/query/sql2/test.sql2.txt?view=markup

       

      Best regards, thanks in advance,

      K.

        • 1. Re: SQL2 | How to add condition on multi-value field?
          rush_pawan Level 4

          Hi,

           

          Below is an example of SQL2 query based on your scenario. you can modify and use it

           

          SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/geometrixx/en]) and (CONTAINS(s.[cq:tags], 'mynspace:mytag') or CONTAINS(s.[cq:tags], 'mynspace:mytagss1'))

           

          where root node path  and tags value you can change as per your need. Below is java formation

           

          StringBuilder query = new StringBuilder();

          query.append("SELECT * FROM [nt:base] AS s WHERE ISDESCENDANTNODE([")

          .append(pathToYourPageArea)   //for example /content/geometrixx/en

          .append("]) AND (CONTAINS(s.[cq:tags],'mynspace:mytag') or CONTAINS(s.[cq:tags],'mynspace:mytagss1'))");

          Query compiledQuery = queryManager.createQuery(query.toString(), Query.JCR_SQL2);

           

          where 'mynspace:mytag' and 'mynspace:mytagss1' is an example and you can change it with variables

           

          I hope it will help you to proceed. Let me know if you need more information.

           

           

          Thanks,

          Pawan