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

[askdctm]

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

[smacdonald2008]

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. 

[askdctm]

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_]

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]

[askdctm]

Hi Kunal,

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

[askdctm]

Hi Kunal,

It is returning zero results.

[Kunal_Gaba_]

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

[Kunal_Gaba_]

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

[askdctm]

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.

[askdctm]

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_]

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

[askdctm]

Hi Kunal,

Will check on this and post back the results.

[askdctm]

Hi Kunal,

The query seems to be working fine now.

A minor clarification required from your end:

1] In case, in the problem statement mentioned above, if we have "ABC" and "DEF" as the search keyword provided on the page, rather than "ABC DEF" [which was mentioned initially], then the below query should work fine correct or some modifications required?

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

Your thoughts on this will be helpful.

[askdctm]

Hi Kunal,

Missed out on making few things clear in the previous post.

I mean when "ABC DEF" is provided as an input in the search page, we are trying to search for occurences of "ABC", "DEF" and "ABC DEF" seperately, rather than a single keyword[i.e, "ABC DEF"], by means of the above query.

[Kunal_Gaba_]

[1] Check the documentation of the contains function. 

By default the search terms separated by whitespace are ANDed together. For example if you fire the following query- 

SELECT * FROM [nt:base] AS s where contains(s.[jcr:title],'Geometrixx Outdoors') 

then you get results which have "Geometrixx Outdoors" combined together. 

However, if you fire the below query- 

SELECT * FROM [nt:base] AS s where contains(s.[jcr:title],'Geometrixx OR Outdoors')

then you get the results which either have Geometrixx or Outdoors in title. 

[1] http://www.day.com/specs/jcr/1.0/6.6.5.2_jcr_contains_Function.html

[askdctm]

Hi Kunal,

Thank you for your reply.

Looks like we are facing some issues with the results returned in the first requirement itself[i.e, when a single keyword is passed as a search parameter on the page], it is returning some unwanted results too.

Checking on it and will keep you posted with our observations.

[askdctm]

Hi Kunal,

For now, please ignore my previous comment as we are only concentrating for search with multiple keywords, leaving single keyword search as-is.
Sorry for the confusion.

In case of two keywords provided in the search page[i.e, "ABC DEF"],the option you mentioned below [#1 or #2(which we are trying)] are giving similar results.

1] In the documentation http://www.day.com/specs/jcr/1.0/6.6.5.2_jcr_contains_Function.html about putting an OR condition 

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

3] The problem is 
a] If we run the query with both LIKE and CONTAINS, it does not properly return the results.
b]if we remove all LIKE clauses and have just CONTAINS clauses and then execute query, it is working fine for exact string matches of input keywords [i.e, only for DEF and not for DEFs or DEFbb etc].

4] So, is it something like there is some issue when LIKE and CONTAINS are used simultaneously[ I mean in our case of multiple keywords] or there is something else to be done here.

Any thoughts/pointers on this will be helpful.

[Kunal_Gaba_ · Accepted solution]

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. 

[askdctm]

Hi Kunal,

Thanks a ton for your reply. After making the above changes, looks like things are working fine.

Will validate once more and keep you posted.

[askdctm]

Hi Kunal,

Did couple of tests now and the query seems to be working perfect.

Thanks a lot for all your help and answering all of the doubts I had.

Once again THANKS A LOT. 

The performance is one aspect due to which we have to go for a querybuilder approach to achieve the same objective. Will initiate a separate thread for that in the forum.