Search or Fetch unique or distinct pages by passing the component name or path

[Saravanan_U]

Hi

I am using Query builder as below to get the list of distinct pages which uses the component (component name will passed as a parameter) in property. I need to pass two parameters, cq:lastReplicationAction=Activate and sling:resourceType=component path. I tried above JSON query but no result and it is failing since the component may be used at any level of page node. E.g, /jcr:content/par/component or /jcr:content/par/mainpar/component

https://host:port/bin/querybuilder.json?1_property=sling:resourceType&1_property.value=COMPONENTPATH...

Thanks in advance

Saran

[smacdonald2008]

"failing since the component may be used at any level of page

"I am not sure this is possible with these out of the box JSON queries without knowing the exact path of the components. In all of the examples - the exact paths are given. See: 

https://docs.adobe.com/docs/en/cq/5-6-1/dam/customizing_and_extendingcq5dam/query_builder.html

 You may need to look at building a more advanced tool that uses the JCR API and then use JCR SQL to get pages and search them looking for the components. This will allow you to build a tool that can perform a more detailed search of the AEM JCR. 

[Saravanan_U]

You mean say, none of the queries will for this (XPATH, SQL2 and query builder). There should be a way as many would have addressed this issue.

If we do the same using JAVA API, then the performance is the challenge.

[smacdonald2008]

You do not get the same level of query functionality as you do using the Java API. 

[Kunal_Gaba_]

If you want to try this with SQL 2 then the following query should work- 

SELECT * FROM [cq:PageContent] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent,[FROM_WHICH_CONTENT_PATH]) AND child.[sling:resourceType]='COMPONENTPATH' AND parent.[cq:lastReplicationAction] = 'Activate'

[Saravanan_U]

Often getting "Query execution resulted in an error!", when I try to execute the query using either in CRXDE LITE or through api. But if the result set is less or minimal I am getting the result. SWITCH "ISDESCENDANTNODE" with "ISCHILDNODE" either wise.

Sample query used: 

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, 'CONTENT_PATH_FROM_WHERE_TO_START') AND (child.[sling:resourceType] = 'COMPONENT_NAME' AND child.[cq:lastReplicationAction] = 'Activate')

and 

SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISDESCENDANTNODE(child,parent) WHERE ISDESCENDANTNODE(parent, 'CONTENT_PATH_FROM_WHERE_TO_START') AND (child.[sling:resourceType] = 'COMPONENT_NAME' AND child.[cq:lastReplicationAction] = 'Activate')

There should be answer for this, definitely as many would have faced this issue for reporting purpose when their website pages are more in number,

[Kunal_Gaba_]

Why don't you use the component report for this use case ?You can customize this report to show only the page activation date which be added as a new column and may be show page path instead of the page link.  

http://localhost:4502/etc/reports/compreport.html

http://localhost:4502/crx/de/libs/cq/reporting/components/compreport/lastmodcol/definitions/queryBuilder

https://docs.adobe.com/docs/en/aem/6-1/develop/operations/dev-reports.html

[Saravanan_U]

Hi Kunal

Thanks for your reply.

Does it work for large volume as I have 3000 pages in my root parent page path "/content/project/rootpath/"

[Kunal_Gaba_]

I think it will work as the reporting query framework does post processing/filtering on the result set returned by just doing a simple query rather than adding the predicate conditions to the query itself for filtering. You can check the out of the box page audit report for checking the performance as the number audit nodes in the repository are even bigger than the number of page nodes themselves. For 3000 pages the component report should work fine. Have you checked it by just accessing the report on your AEM instance ?

[Saravanan_U]

I always get an error in console  as "Query execution resulted in an error!"

I am using "http://localhost:4502/crx/de/index.jsp" --> Tools --> Query

[Kunal_Gaba_]

Since you have too many nodes being returned in the resultset,  is it good to consider limiting the results ? Or else, Instead of running the query on root node why not run it on subpaths separately for limiting the results. 

http://help-forums.adobe.com/content/adobeforums/en/experience-manager-forum/adobe-experience-manage...

http://stackoverflow.com/questions/31030159/jsp-query-getresult-getnodes-time-consuming