Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Query Builder

Avatar

Level 4

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?

1 Reply

Avatar

Community Advisor

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

DEBAL_DAS_0-1651671310112.png

Author's entries -

DEBAL_DAS_1-1651671331618.pngDEBAL_DAS_2-1651671343297.png

 

Persisted data in CRXDE -

DEBAL_DAS_3-1651671380160.png

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

DEBAL_DAS_4-1651671438467.png

 

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.

 

Avatar

Level 4
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...)? 

Avatar

Community Advisor

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

Avatar

Level 4

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" ???

 

Avatar

Community Advisor
/**
 * 
 */
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.

Avatar

Level 4

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

Avatar

Community Advisor

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