Get unique page name(s) from the mentioned JCR-SQL2 query | Community
Skip to main content
A
askdctm
Level 9
January 5, 2016
Solved

Get unique page name(s) from the mentioned JCR-SQL2 query

  • January 5, 2016
  • 19 replies
  • 5577 views

Hi All,

We have a below JCR-SQL2 query as below :

SELECT DISTINCT Path FROM [nt:base] AS s WHERE ISDESCENDANTNODE([/content/x/y]) AND (CONTAINS(s.[cq:tags],'ABC DEF^600') OR CONTAINS(s.blocktitle,'ABC DEF^400') OR (s.blocktitle LIKE 'ABC DEF%') OR CONTAINS(s.bodycopy,'ABC DEF^100') OR (s.bodycopy LIKE 'ABC DEF%') OR CONTAINS(s.headingText,'ABC DEF^10') OR (s.headingText LIKE 'ABC DEF%') OR CONTAINS(s.Author,'ABC DEF^400') OR (s.Author LIKE 'ABC DEF%') OR CONTAINS(s.headline,'ABC DEF^2') OR (s.headline LIKE 'ABC DEF%') OR CONTAINS(s.[jcr:title],'ABC DEF') OR (s.[jcr:title] LIKE 'ABC DEF%')) AND ( NOT [hideInSearch] IS NOT NULL) ORDER BY 'jcr:lastModified'

1] Most of the properties queried are present at jcr:content level of page and some of them are present at component level [for example : if a page has component c1, headingText property is present at the component level and not at the jcr:content level of the page ].

2] "ABC DEF" is the search keyword provided on the page

3] Suppose we have a page, with few components in it which satisfies the criteria, the result would be returned in the form of 

/content/abc/def/jcr:content/component1

/content/abc/def/jcr:content/component2

/content/abc/def/jcr:content/component3

4] I would want this query to return just /content/abc/def instead of results given as in #3[i.e, even if several components in a page matches the criteria, at the end I just want that particular page to be returned, instead of seperate values ].

5] Can someone please let me know how this can be achieved.

Any thoughts/pointers on this will be really really helpful

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by Kunal_Gaba_

    I would then remove all LIKE clauses and use asterisk (*) as wild card character in contain clauses like CONTAINS(s.[jcr:title],'DEF*'). This will return for DEF and DEFbb both. 

    19 replies

    smacdonald2008
    smacdonald2008
    Level 10
    January 5, 2016

    You are getting these path values because you are using JCR SQL2 to search for JCR paths: 

    SELECT DISTINCT Path FROM ...

    You want:

    "/content/abc/def"

    You may have to iterate through the result set and use application logic to amend this value to meet your business requirements. 

    In addition - you are also using many OR operations - which will increase your result set. 

      A
      askdctmAuthor
      Level 9
      January 6, 2016

      Hi Scott,

      Thank you for your reply.

      While trying to handle this via application code, we are facing some other issues. That being the reason, we wanted to somehow get unique page paths [like "/content/abc/def"] via this query itself.

      Kunal_Gaba_
      Kunal_Gaba_
      January 6, 2016

      Try the below SQL 2 query - 

      SELECT * FROM [cq:Page] AS parent INNER JOIN [nt:base] As s ON ISCHILDNODE(parent,s) WHERE ISDESCENDANTNODE(parent,[/content/x/y]) AND (CONTAINS(s.[cq:tags],'ABC DEF^600') OR CONTAINS(s.blocktitle,'ABC DEF^400') OR (s.blocktitle LIKE 'ABC DEF%') OR CONTAINS(s.bodycopy,'ABC DEF^100') OR (s.bodycopy LIKE 'ABC DEF%') OR CONTAINS(s.headingText,'ABC DEF^10') OR (s.headingText LIKE 'ABC DEF%') OR CONTAINS(s.Author,'ABC DEF^400') OR (s.Author LIKE 'ABC DEF%') OR CONTAINS(s.headline,'ABC DEF^2') OR (s.headline LIKE 'ABC DEF%') OR CONTAINS(s.[jcr:title],'ABC DEF') OR (s.[jcr:title] LIKE 'ABC DEF%'))  AND ( NOT parent.[hideInSearch] IS NOT NULL) ORDER BY parent.[jcr:lastModified]
      A
      askdctmAuthor
      Level 9
      January 6, 2016

      Hi Kunal,

      Thank you for your reply. Will check on this and let you know how it goes.

      A
      askdctmAuthor
      Level 9
      January 6, 2016

      Hi Kunal,

      It is returning zero results.

      Kunal_Gaba_
      Kunal_Gaba_
      January 6, 2016

      Check all the conditions in the query again. I think parent.[hideInSearch] should be s.[hideInSearch]

      Kunal_Gaba_
      Kunal_Gaba_
      January 6, 2016

      Check all the conditions in the query again. I think parent.[hideInSearch] should be s.[hideInSearch]

      A
      askdctmAuthor
      Level 9
      January 6, 2016

      Hi Kunal,

      I tried with s.[hideInSearch] and it is returning zero results.

      Will try and see again ,if I can get anything on this.

      A
      askdctmAuthor
      Level 9
      January 6, 2016

      Hi Kunal,

      When tried, the query is working fine with a very minor change of ISCHILDNODE(s,parent). Thanks a lot for this query.

      However, one additional problem we are facing is it is looking for properties at page jcr:content level. If this page has a component c1, and if that has any  property it is not taking that into consideration. The reason being, most of the properties we are looking at page jcr:content level, however some of the properties are present at the component level inside the page.

      Not sure how to go about this. Any thoughts/pointers on this will be really helpful.

      Kunal_Gaba_
      Kunal_Gaba_
      January 6, 2016

      You should then replace ISCHILDNODE function with ISDESCENDANTNODE (s,parent)

      Show more replies
      Terms & ConditionsAccessibility statement

      Loading footer...