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

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

    KosPol Level 1



      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,


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



          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.