SQL 2 query

[Sachin_Chauhan1]

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 

[arunpatidar]

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-cons...

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.

[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