[AEM6.5] JCR SQL2 Query to fetch the count of node under a component | Community
Skip to main content
tushaar_srivastava
tushaar_srivastava
Level 6
April 29, 2024
Solved

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

  • April 29, 2024
  • 2 replies
  • 2809 views

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 

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by lukasz-m

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())); } } }

2 replies

lukasz-m
Community Advisor
lukasz-mCommunity AdvisorAccepted solution
Community Advisor
April 29, 2024

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())); } } }
    tushaar_srivastava
    tushaar_srivastavaAuthor
    Level 6
    April 29, 2024

    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.

      Harwinder-singh
      Community Advisor
      Harwinder-singhCommunity Advisor
      Community Advisor
      April 29, 2024

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

        Terms & ConditionsAccessibility statement

        Loading footer...