Expand my Community achievements bar.

SOLVED

[AEM6.5] JCR SQL2 Query to fetch the count of node under a component

Avatar

Level 7

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.

tushaar_srivastava_0-1714392208916.png

 

 

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 

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

Hi @tushaar_srivastava,

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.

  1. It searches for all pages with /conf/migration/settings/wcm/templates/taxonomy-template template, and that contains child with name codesection
  2. It checks if given page has more than 1000 direct child nodes under codesection node.
  3. Adds any page that fulfills criteria from step 1 and 2 into result set.
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()));
        } 
    }
}

View solution in original post

3 Replies

Avatar

Correct answer by
Community Advisor

Hi @tushaar_srivastava,

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.

  1. It searches for all pages with /conf/migration/settings/wcm/templates/taxonomy-template template, and that contains child with name codesection
  2. It checks if given page has more than 1000 direct child nodes under codesection node.
  3. Adds any page that fulfills criteria from step 1 and 2 into result set.
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()));
        } 
    }
}

Avatar

Level 7

Yes @lukasz-m , Thanks for clarifying, I was trying to get the count through SQL2, thinking we cannot.
let me work on Servlet in order to achieve this.

Avatar

Community Advisor

@tushaar_srivastava  JCR SQL2 doesn't support any Aggregation functions like Count , sum, min, max etc.