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

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.

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.

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.

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.

[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

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.

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.

Hi Kunal,

Will check on this and post back the results.

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