Query Builder

[anasustic]

Can someone help me with this query.

I am trying to find all the nodes under content that have a sling:resourceType=

components/content/home/homeHeroTeaser 

I wrote the above as:

String query = '''
    SELECT * FROM [nt:base] AS n
    WHERE ISDESCENDANTNODE([/content])
    AND n.[sling:resourceType]='components/content/home/homeHeroTeaser'
'''

 In addition I would like add to this query a check that a node has a property link that has a value. How do I do that?

[DEBAL_DAS]

I have added Teaser component in we.retail page here -

Author's entries -

Persisted data in CRXDE -

Query builder query has given below -

path=/content/we-retail
type=nt:unstructured
1_property=sling:resourceType
1_property.value=weretail/components/content/teaser
2_property=linkURL
2_property.operation=exists
p.limit=-1

Hope this will help you. But I would request you to check the node structure of homeHeroTeaser component in CRXDE then we can decide whether we should add following check in "a node has a property link that has a value" in query or code.

[anasustic]

1_property=sling:resourceType
1_property.value=weretail/components/content/teaser
2_property=linkURL
2_property.operation=exists
p.limit=-1

Hi. Thanks so much for creating the test content and writing the query. Can you please explain the lines above (ie. 1_property, 1_property.value...)? 

[DEBAL_DAS]

My requirement is to find all the nodes under /content that have  sling:resourceType=weretail/components/content/teaser and also check that a node has a property linkURL that has a value, it means I need to consider two different properties sling:resourceType and linkURL here.

When using the property predicate multiple times, then I have to add the number prefixes again as shown below -

type=cq:Page
1_property=jcr:content/cq:template
1_property.value=/apps/geometrixx/templates/homepage
2_property=jcr:content/jcr:title
2_property.value=English

[anasustic]

Awesome. Thank you.

How would you write it as a query string?

SELECT * FROM [nt:base] AS n
WHERE ISDESCENDANTNODE([/content])
AND n.[sling:resourceType]='/apps/geometrixx/templates/homepage' 
AND n.[linkURL] IS NOT NULL" ???

[DEBAL_DAS]

/**
 * 
 */
package com.aem.demo.core.servlets;

import java.io.IOException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Objects;
import java.util.TreeMap;

import javax.jcr.Session;
import javax.servlet.Servlet;

import org.apache.sling.api.SlingHttpServletRequest;
import org.apache.sling.api.SlingHttpServletResponse;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.servlets.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.aem.demo.core.services.WriteDataToFile;
import com.day.cq.search.PredicateGroup;
import com.day.cq.search.Query;
import com.day.cq.search.QueryBuilder;
import com.day.cq.search.result.SearchResult;
import com.google.common.base.Strings;

/**
 * @author debal
 * 
 *         This servlet is used to generate report associated with activated
 *         page or activated assets only Report will be generated for AEM sites
 *         under specific content path
 */
@Component(service = Servlet.class, property = { "sling.servlet.paths=" + "/bin/reports/resource",
		"sling.servlet.methods=" + HttpConstants.METHOD_POST })
public class ResourceResportServlet extends SlingAllMethodsServlet {

	private final Logger logger = LoggerFactory.getLogger(ResourceResportServlet.class);

	private static final long serialVersionUID = 8383266648883534070L;
	@Reference
	private QueryBuilder queryBuilder;

	@Reference
	WriteDataToFile writeDataToFile;

	protected void doPost(SlingHttpServletRequest slingHttpServletRequest,
			SlingHttpServletResponse slingHttpServletResponse) {

		int rowNumber = 0;
		Map<String, String> map = new HashMap<String, String>();
		Map<Integer, String> resourceMap = new TreeMap<>();
		String resourceSearchPath = slingHttpServletRequest.getParameter("searchPath");
		String lastReplicatedDate = slingHttpServletRequest.getParameter("lastPublishedDate");
		logger.info("**** Report generation has been initiated under {}" + resourceSearchPath);
		logger.info("**** Report generation has been generated from {}" + lastReplicatedDate);

		String rseourceType = null;

		if (!Strings.isNullOrEmpty(resourceSearchPath) && !Strings.isNullOrEmpty(lastReplicatedDate)) {

			try (ResourceResolver resourceResolver = slingHttpServletRequest.getResourceResolver()) {

				Session session = resourceResolver.adaptTo(Session.class);

				Resource resource = resourceResolver.getResource(resourceSearchPath);

				if (Objects.nonNull(resource)) {

					String resourcepath = resource.getPath();
					if (resource.isResourceType("cq:Page")) {
						rseourceType = "cq:Page";
					} else if (resourcepath.contains("/dam/")) {
						rseourceType = "dam:Asset";
					}

					logger.info("**** Resource Type {}" + rseourceType);
					/*
					 * create query description as hash map (simplest way, same as form post)
					 */
					map.put("path", resourceSearchPath.trim());
					map.put("type", rseourceType.trim());

					map.put("1_property", "jcr:content/cq:lastReplicationAction");
					map.put("1_property.value", "Activate");
					map.put("2_daterange.property", "jcr:content/cq:lastReplicated");
					map.put("2_daterange.lowerBound", lastReplicatedDate);
					map.put("p.limit", "-1");

					Query searchquery = queryBuilder.createQuery(PredicateGroup.create(map), session);

					

					SearchResult searchResult = searchquery.getResult();
					logger.info("**** Number of Total Matches {}" + searchResult.getTotalMatches());
					Iterator<Resource> resources = searchResult.getResources();
					while (resources.hasNext()) {

						Resource searchResultResource = resources.next();

						String searchrseourcePath = searchResultResource.getPath();
						slingHttpServletResponse.getWriter().write(searchrseourcePath);
						resourceMap.put(rowNumber++, searchrseourcePath);

					}
					writeDataToFile.addDataToFile(resourceMap, slingHttpServletResponse);
				}

			} catch (IOException e) {

				e.printStackTrace();
			}
			;
		}

	}

}

You can refer this sample code. Please refer the approach like how could we form query builder query in our code.

[anasustic]

Great thank you. Your example is with Predicates just like in the AEM Query Builder.

[DEBAL_DAS]

Are you planning to use query builder or JCR SQL2 query here? 

SELECT * FROM [nt:base] AS n
WHERE ISDESCENDANTNODE([/content])
AND n.[sling:resourceType]='/apps/geometrixx/templates/homepage' 
AND n.[linkURL] IS NOT NULL" ???

The above one is a JCR-SQL2 query

Then refer the below one -

		try {

			Session session = getResolver.getWorkflowServiceResolver().adaptTo(Session.class);
			WorkflowSession workflowSession = getResolver.getWorkflowServiceResolver().adaptTo(WorkflowSession.class);
			String failedWorkItemQuery = "SELECT * FROM [cq:WorkItem] AS s WHERE ISDESCENDANTNODE([/var/workflow/instances/server0]) and subType='FailureItem' and status = 'ACTIVE'";
			if (Objects.nonNull(session)) {
				QueryManager queryManager = session.getWorkspace().getQueryManager();
				Query query = queryManager.createQuery(failedWorkItemQuery, Query.JCR_SQL2);
				QueryResult queryResult = query.execute();
				NodeIterator nodeIterator = queryResult.getNodes();
				while (nodeIterator.hasNext()) {
					Node nextNode = nodeIterator.nextNode();
					WorkItem workItem = workflowSession.getWorkItem(nextNode.getPath());
					slingHttpServletResponse.getWriter().write(
							workItem.getItemSubType().concat("****************").concat(workItem.getNode().getTitle()));
				}

			}

		} catch (LoginException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (RepositoryException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (WorkflowException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}