Hi Team,
I am trying to write a JCR SQL2 query in AEM 6.5 to get the list of all pages of template /conf/migration/settings/wcm/templates/taxonomy-template which is having more than 1000+ nodes under it.
The scenario i have multiple pages under /content/project.
There are few pages which belongs to /conf/migration/settings/wcm/templates/taxonomy-template
Example: /content/project/page1 And it has multifeild components called as taxonomy which stores the multiple values in different nodes And the structure is like /content/project/us/en/about-us/jcr:content/root/container/taxonomy/codesection.
And inside this we have long list of node.
Now I want to fetch the list of all pages under path /content/project with template type /conf/migration/settings/wcm/templates/taxonomy-template and the nodes will be more than 1000+ under componet
*/jcr:content/root/container/taxonomy/codesection/
I am trying to do in a way like:
SELECT * FROM [nt:base] AS page
WHERE ISDESCENDANTNODE(page, '/content/project')
AND page.[jcr:content/cq:template] = '/conf/migration/settings/wcm/templates/taxonomy-template'
AND (SELECT COUNT(*) FROM [nt:base] AS nodes WHERE ISDESCENDANTNODE(nodes, page) AND NAME(nodes) = 'codesection') > 1000
But while running it throws ParseException.
Can anyone please guide how to achive the list of pages have more than 1000+ nodes under a single components in page.
Thanks
@kautuk_sahni @lukasz-m @EstebanBustamante
Solved! Go to Solution.
Topics help categorize Community content and increase your ability to discover relevant content.
Views
Replies
Total Likes
Count is not supported by JCR SQL2, so this will not work. I do not think you can achieve your goal using query only. Most likely you will need combine results from query with some java code that will do the rest.
You can have a look on below code snippet.
import javax.jcr.query.Query;
import org.apache.commons.collections4.IteratorUtils;
String query = "SELECT * FROM [cq:Page] AS page INNER JOIN [nt:base] AS"
+ "childnode ON ISDESCENDANTNODE(childnode, page) WHERE ISDESCENDANTNODE(page, '/content/project')"
+ "AND name(childnode) = 'codesection' AND page.[jcr:content/cq:template] = '/conf/migration/settings/wcm/templates/taxonomy-template'";
// set to store results
Set<Page> pages = new HashSet<Page>();
PageManager pageManager = resourceResolver.adaptTo(PageManager.class);
Iterator<Resource> resourceIterator = resourceResolver.findResources(query, Query.JCR_SQL2);
if (resourceIterator != null) {
while (resourceIterator.hasNext()) {
Resource resource = resourceIterator.next();
if (IteratorUtils.size(resource.listChildren()) > 1000) {
pages.add(pageManager.getContainingPage(resource.getPath()));
}
}
}
Count is not supported by JCR SQL2, so this will not work. I do not think you can achieve your goal using query only. Most likely you will need combine results from query with some java code that will do the rest.
You can have a look on below code snippet.
import javax.jcr.query.Query;
import org.apache.commons.collections4.IteratorUtils;
String query = "SELECT * FROM [cq:Page] AS page INNER JOIN [nt:base] AS"
+ "childnode ON ISDESCENDANTNODE(childnode, page) WHERE ISDESCENDANTNODE(page, '/content/project')"
+ "AND name(childnode) = 'codesection' AND page.[jcr:content/cq:template] = '/conf/migration/settings/wcm/templates/taxonomy-template'";
// set to store results
Set<Page> pages = new HashSet<Page>();
PageManager pageManager = resourceResolver.adaptTo(PageManager.class);
Iterator<Resource> resourceIterator = resourceResolver.findResources(query, Query.JCR_SQL2);
if (resourceIterator != null) {
while (resourceIterator.hasNext()) {
Resource resource = resourceIterator.next();
if (IteratorUtils.size(resource.listChildren()) > 1000) {
pages.add(pageManager.getContainingPage(resource.getPath()));
}
}
}
@tushaar_srivastava JCR SQL2 doesn't support any Aggregation functions like Count , sum, min, max etc.