SQL 2 query | Community
Skip to main content
S
Sachin_Chauhan1
August 21, 2024
Solved

SQL 2 query

  • August 21, 2024
  • 2 replies
  • 627 views

Business want the list of pages where a component is not added in page. Can I have a sql 2 query for the requirement. I tried many queries but they are giving all the pages because query traversing to all the node of the page and there are other components thats why the it matches the query and giving result. Could you please help me here 

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 MukeshYadav_

Hi @sachin_chauhan1 ,

You may use below servlet, I tested on around 2000 nodes you may narrow down rootPath is case timed out

http://localhost:4502/bin/checkComponents?rootPath=/content/project&componentType=proejct/components/cmp1

 

package com.project.group.v2.core.servlets;//write your package name import com.day.cq.search.PredicateGroup; import com.day.cq.search.Query; import com.day.cq.search.QueryBuilder; import com.day.cq.search.result.Hit; import com.day.cq.search.result.SearchResult; import com.day.cq.wcm.api.Page; import com.fasterxml.jackson.databind.ObjectMapper; import org.apache.sling.api.SlingHttpServletRequest; import org.apache.sling.api.SlingHttpServletResponse; import org.apache.sling.api.resource.ResourceResolver; import org.apache.sling.api.servlets.SlingSafeMethodsServlet; import org.osgi.service.component.annotations.Component; import org.osgi.service.component.annotations.Reference; import javax.jcr.Session; import javax.servlet.Servlet; import javax.servlet.ServletException; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import static com.day.cq.commons.jcr.JcrConstants.NT_UNSTRUCTURED; import static com.day.cq.wcm.api.NameConstants.NT_PAGE; import static org.apache.sling.jcr.resource.api.JcrResourceConstants.SLING_RESOURCE_TYPE_PROPERTY; @Component( service = {Servlet.class}, property = { "sling.servlet.paths=/bin/checkComponents" } ) public class ComponentCheckerServlet extends SlingSafeMethodsServlet { @Reference private transient QueryBuilder queryBuilder; private String componentType; @Override protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServletException, IOException { String rootPath = request.getParameter("rootPath"); String componentType = request.getParameter("componentType"); if (rootPath == null || componentType == null) { response.setStatus(SlingHttpServletResponse.SC_BAD_REQUEST); response.getWriter().write("rootPath and componentType parameters are required."); return; } try { List<String> pagesWithoutComponent = new ArrayList<>(); Map<String, String> queryMap = new HashMap<>(); queryMap.put("path", rootPath); queryMap.put("p.limit", "-1"); queryMap.put("p.offset", "0"); queryMap.put("type", NT_PAGE); queryMap.put("1_property", "jcr:content/jcr:primaryType"); queryMap.put("1_property.value", "cq:PageContent"); ResourceResolver resourceResolver = request.getResourceResolver(); Query query = queryBuilder.createQuery(PredicateGroup.create(queryMap), resourceResolver.adaptTo(Session.class)); SearchResult result = query.getResult(); for (Hit hit : result.getHits()) { Page page = hit.getResource().adaptTo(Page.class); if (page != null && !hasComponent(page, componentType, resourceResolver)) { pagesWithoutComponent.add(page.getPath()); } } response.setContentType("application/json"); response.getWriter().write(new ObjectMapper().writeValueAsString(pagesWithoutComponent));//we can use Gson or JSONArray or jackson } catch (Exception e) { response.setStatus(SlingHttpServletResponse.SC_INTERNAL_SERVER_ERROR); response.getWriter().write("An error occurred: " + e.getMessage()); } } private boolean hasComponent(Page pageResource, String componentType, ResourceResolver resourceResolver) { HashMap<String, String> queryMap = new HashMap<>(); queryMap.put("path", pageResource.getPath()+"/jcr:content"); queryMap.put("p.offset", "0"); queryMap.put("type", NT_UNSTRUCTURED); queryMap.put("1_property", SLING_RESOURCE_TYPE_PROPERTY); queryMap.put("1_property.value", componentType); SearchResult searchResult = queryBuilder.createQuery(PredicateGroup.create(queryMap), resourceResolver.adaptTo(Session.class)).getResult(); return searchResult != null && searchResult.getHits() != null && !searchResult.getHits().isEmpty(); } }

 

Thanks

2 replies

arunpatidar
Community Advisor
arunpatidarCommunity Advisor
Community Advisor
August 21, 2024

Hi @sachin_chauhan1 
You already have OOTB dashboard for component usage, you can check where specific component i used

https://experienceleague.adobe.com/en/docs/experience-manager-learn/sites/components/components-console-feature-video-use

You can also have same using ACS Commons Report : https://adobe-consulting-services.github.io/acs-aem-commons/features/report-builder/index.html 

 

In your case, it would be all pages minus pages with specific component.

 

However you can try groovy to extract the list but only with SQL2 it may not be possible.

Arun Patidar
    MukeshYadav_
    Community Advisor
    MukeshYadav_Community AdvisorAccepted solution
    Community Advisor
    August 21, 2024

    Hi @sachin_chauhan1 ,

    You may use below servlet, I tested on around 2000 nodes you may narrow down rootPath is case timed out

    http://localhost:4502/bin/checkComponents?rootPath=/content/project&componentType=proejct/components/cmp1

     

    package com.project.group.v2.core.servlets;//write your package name import com.day.cq.search.PredicateGroup; import com.day.cq.search.Query; import com.day.cq.search.QueryBuilder; import com.day.cq.search.result.Hit; import com.day.cq.search.result.SearchResult; import com.day.cq.wcm.api.Page; import com.fasterxml.jackson.databind.ObjectMapper; import org.apache.sling.api.SlingHttpServletRequest; import org.apache.sling.api.SlingHttpServletResponse; import org.apache.sling.api.resource.ResourceResolver; import org.apache.sling.api.servlets.SlingSafeMethodsServlet; import org.osgi.service.component.annotations.Component; import org.osgi.service.component.annotations.Reference; import javax.jcr.Session; import javax.servlet.Servlet; import javax.servlet.ServletException; import java.io.IOException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import static com.day.cq.commons.jcr.JcrConstants.NT_UNSTRUCTURED; import static com.day.cq.wcm.api.NameConstants.NT_PAGE; import static org.apache.sling.jcr.resource.api.JcrResourceConstants.SLING_RESOURCE_TYPE_PROPERTY; @Component( service = {Servlet.class}, property = { "sling.servlet.paths=/bin/checkComponents" } ) public class ComponentCheckerServlet extends SlingSafeMethodsServlet { @Reference private transient QueryBuilder queryBuilder; private String componentType; @Override protected void doGet(SlingHttpServletRequest request, SlingHttpServletResponse response) throws ServletException, IOException { String rootPath = request.getParameter("rootPath"); String componentType = request.getParameter("componentType"); if (rootPath == null || componentType == null) { response.setStatus(SlingHttpServletResponse.SC_BAD_REQUEST); response.getWriter().write("rootPath and componentType parameters are required."); return; } try { List<String> pagesWithoutComponent = new ArrayList<>(); Map<String, String> queryMap = new HashMap<>(); queryMap.put("path", rootPath); queryMap.put("p.limit", "-1"); queryMap.put("p.offset", "0"); queryMap.put("type", NT_PAGE); queryMap.put("1_property", "jcr:content/jcr:primaryType"); queryMap.put("1_property.value", "cq:PageContent"); ResourceResolver resourceResolver = request.getResourceResolver(); Query query = queryBuilder.createQuery(PredicateGroup.create(queryMap), resourceResolver.adaptTo(Session.class)); SearchResult result = query.getResult(); for (Hit hit : result.getHits()) { Page page = hit.getResource().adaptTo(Page.class); if (page != null && !hasComponent(page, componentType, resourceResolver)) { pagesWithoutComponent.add(page.getPath()); } } response.setContentType("application/json"); response.getWriter().write(new ObjectMapper().writeValueAsString(pagesWithoutComponent));//we can use Gson or JSONArray or jackson } catch (Exception e) { response.setStatus(SlingHttpServletResponse.SC_INTERNAL_SERVER_ERROR); response.getWriter().write("An error occurred: " + e.getMessage()); } } private boolean hasComponent(Page pageResource, String componentType, ResourceResolver resourceResolver) { HashMap<String, String> queryMap = new HashMap<>(); queryMap.put("path", pageResource.getPath()+"/jcr:content"); queryMap.put("p.offset", "0"); queryMap.put("type", NT_UNSTRUCTURED); queryMap.put("1_property", SLING_RESOURCE_TYPE_PROPERTY); queryMap.put("1_property.value", componentType); SearchResult searchResult = queryBuilder.createQuery(PredicateGroup.create(queryMap), resourceResolver.adaptTo(Session.class)).getResult(); return searchResult != null && searchResult.getHits() != null && !searchResult.getHits().isEmpty(); } }

     

    Thanks

      Terms & ConditionsAccessibility statement

      Loading footer...