Need SQL2 query for AEM 6.2

[thiszparveen]

Hi,

Please help me in writing SQL2 query to search for all the pages containing custom component property.

I have custom "bodycopy" component which has a property called "myproperty". Below are the sample paths. 

Conditions:
I can have any number of bodycopy components in "head-parsys" and at any level.
Fulltext search
Query should be performed on cq:Page

/content/mysite/jcr:content/head-parsys/bodycopy
/content/mysite/jcr:content/head-parsys/bodycopy_0
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy_0

Below is the sample: 

select * from [cq:Page] as a where contains([dynamic/path/to/mycomponent/myproperty],'test') and isdescendantnode(a, '/content/mysite')

Thanks in advance.

[smacdonald2008]

What is the result set of this query?

[thiszparveen]

CQ:Page

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/mysite]) and CONTAINS(s.[jcr:content/head-parsys/mycomponent/myproperty], 'test') - Working

"head-parsys/mycomponent" is dynamic and can be of any levels. when I replace the values with * also not working

SELECT * FROM [cq:Page] AS s WHERE ISDESCENDANTNODE([/content/mysite]) and CONTAINS(s.[jcr:content/*/*/myproperty], 'test') - Not Working in AEM 6.2. 

[smacdonald2008]

Looks like a path wild card syntax issue -- look here: 

http://stackoverflow.com/questions/13951933/jcr-sql2-query-wildcard-search-not-working

[thiszparveen]

I have gone through it. But my question is when we search for component properties, the result will not be of type cq:Page, instead nt:unstructured. Below is the sample result:

/content/mysite/jcr:content/head-parsys/bodycopy
/content/mysite/jcr:content/head-parsys/bodycopy_0
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy
/content/mysite/jcr:content/head-parsys/parsys_1/bodycopy_0

So, the below query is working:

SELECT * FROM [nt:unstructured] AS s WHERE ISDESCENDANTNODE([/content/mysite]) and CONTAINS(s.[myproperty], 'test'); 

The same query is not qorking with [cq:Page]. So, is it not a expensive call to query on [nt:unstructured]? or that is the only way to search for component properties? 

[smacdonald2008]

"The same query is not qorking with [cq:Page]" 

When working with cq:page - you are restricted on the props you can add to this node, while using nt:unstructured - you can add any prop you need. So you have more flexibilty when working with nt:unstructured.

You cannot add a property named myproperty to a cq:page node. That is why the query will not work,  

[kolpur]

@thiszparveen,

You can try something like this:

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISDESCENDANTNODE(child, parent) WHERE ISDESCENDANTNODE(parent, '/content/geometrixx/en/products') AND child.[sling:resourceType]='geometrixx/components/title'

This will return all [cq:Page] nodes that have nodes with property [sling:resourceType] set to geometrixx/components/title.

You can modify this for your property.