Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.
SOLVED

query regarding dam asset publish status

Avatar

Level 1

Hi,

I want to make an SQL2 query which will catch the unpublished dam assets and also the page path where those same assets are used.

Can somebody help me with that.

 

Thanks in advance

@nitesh_kumar @arunpatidar @lukasz-m @DEBAL_DAS 

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Could you please give a try with below code [servlet] -

/**
 * 
 */
package com.task.core.servlets;

import java.io.IOException;
import java.util.Map;
import java.util.Objects;

import javax.jcr.Node;
import javax.jcr.NodeIterator;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.servlet.Servlet;

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.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;

import com.task.core.services.JcrUtility;
import com.day.cq.wcm.commons.ReferenceSearch;
import com.day.cq.wcm.commons.ReferenceSearch.Info;

/**
 * @author debal
 *
 */
@Component(service = Servlet.class, property = { "sling.servlet.paths=" + "/bin/assetreport",
		"sling.servlet.methods=" + HttpConstants.METHOD_GET })
public class UnpublishedImageReferenceDetailsServlet extends SlingAllMethodsServlet{

	@Reference
	private JcrUtility jcrUtility;

	protected void doGet(SlingHttpServletRequest slingHttpServletRequest,
			SlingHttpServletResponse slingHttpServletResponse) {

		try {
			ResourceResolver resourceResolver = jcrUtility.getResourceResolver();
			Session session = resourceResolver.adaptTo(Session.class);

			String failedWorkItemQuery = "SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE([/content/dam/we-retail/en]) and [jcr:content/cq:lastReplicated] is  null";
			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();
					String assetPath = nextNode.getPath();
					Map<String, Info> search = new ReferenceSearch().search(resourceResolver, assetPath, -1, 0);
					for (Map.Entry<String, Info> entry : search.entrySet()) {

						Info info = entry.getValue();
						for (String p : info.getProperties()) {
							String pagepath = info.getPage().getPath();
							slingHttpServletResponse.getWriter().println(assetPath.concat("***********").concat(pagepath));

						}

					}
				}
			}
		}

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

	}
}

 

/**
 * 
 */
package com.task.core.services;

import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;



/**
 * @author debal
 * 
 *         This service will be used as a utility and it will help us to get
 *         resource resolver object  and close resource resolver
 *
 */
public interface JcrUtility {

	public ResourceResolver getResourceResolver();

	public void closeResourceResolver(ResourceResolver resourceResolver);
	
	
}

 

/**
 * 
 */
package com.task.core.impl;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

import javax.jcr.Session;

import org.apache.sling.api.resource.LoginException;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.resource.ResourceResolverFactory;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.task.core.services.JcrUtility;

/**
 * @author debal
 *
 */
@Component(service = JcrUtility.class, immediate = true) 
public class JcrUtilityImpl implements JcrUtility {

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

	@Reference
	ResourceResolverFactory resourceResolverFactory;
	
	

	@Override
	public ResourceResolver getResourceResolver() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put(resourceResolverFactory.SUBSERVICE, "readWriteService");
		ResourceResolver serviceResourceResolver = null;
		try {

			serviceResourceResolver = resourceResolverFactory.getServiceResourceResolver(map);
		} catch (LoginException e) {
			logger.error("Could not get service user [ {} ]", "demoSystemUser", e.getMessage());
		}
		return serviceResourceResolver;

	}

	@Override
	public void closeResourceResolver(ResourceResolver resourceResolver) {
		if (Objects.nonNull(resourceResolver)) {
			resourceResolver.close();
		}

	}

	
}

 

 

I am using JCR_SQL2 query below to identify unpublished assets

SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE([/content/dam/we-retail/en]) and [jcr:content/cq:lastReplicated] is null 

View solution in original post

8 Replies

Avatar

Level 7

Try following query to get unpublished assests:

 

SELECT * FROM [nt:base] AS comp WHERE ISDESCENDANTNODE (comp, '<Put here your path from your dam') AND comp.[jcr:primaryType] =
"dam:AssetContent" and AND comp.[jcr:primaryType] = "dam:AssetContent" and comp.[cq:lastReplicationAction] = 'Deactivate'

 

To get the path wich a dam is used, take a look at 

com.adobe.granite.references.ReferenceAggregator
You have to write a servlet to use this fearture.

Avatar

Community Advisor

Hi @Amir_khan4 

As mentioned by @Magicr you can only use the Query to get unpublished assets and with that result set write custom logic to get the references if the assets in pages.

You cannot combine both in one query

Hope this is helpful.

Avatar

Community Advisor

Hi @Amir_khan4 

 

QQ: Do you want to know the unpublished dam status and the pages that the assets are used before publishing? If that's the use-case then you might not be able to achieve it as AEM would already remove the asset references.

 

Thanks,

Kiran Vedantam.

Avatar

Community Advisor

SELECT * FROM [cq:Page] AS parent INNER JOIN [nt:unstructured] AS child ON ISDESCENDANTNODE(child,parent)WHERE ISDESCENDANTNODE(parent,[CONTENTPATH]) AND child.[sling:resourceType]='ASSET_RESOURCE_TYOE' AND parent.[cq:lastReplicationAction] = 'Activate'

 

You can use this query for finding the asset references.

Avatar

Correct answer by
Employee Advisor

Could you please give a try with below code [servlet] -

/**
 * 
 */
package com.task.core.servlets;

import java.io.IOException;
import java.util.Map;
import java.util.Objects;

import javax.jcr.Node;
import javax.jcr.NodeIterator;
import javax.jcr.RepositoryException;
import javax.jcr.Session;
import javax.jcr.query.Query;
import javax.jcr.query.QueryManager;
import javax.jcr.query.QueryResult;
import javax.servlet.Servlet;

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.HttpConstants;
import org.apache.sling.api.servlets.SlingAllMethodsServlet;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;

import com.task.core.services.JcrUtility;
import com.day.cq.wcm.commons.ReferenceSearch;
import com.day.cq.wcm.commons.ReferenceSearch.Info;

/**
 * @author debal
 *
 */
@Component(service = Servlet.class, property = { "sling.servlet.paths=" + "/bin/assetreport",
		"sling.servlet.methods=" + HttpConstants.METHOD_GET })
public class UnpublishedImageReferenceDetailsServlet extends SlingAllMethodsServlet{

	@Reference
	private JcrUtility jcrUtility;

	protected void doGet(SlingHttpServletRequest slingHttpServletRequest,
			SlingHttpServletResponse slingHttpServletResponse) {

		try {
			ResourceResolver resourceResolver = jcrUtility.getResourceResolver();
			Session session = resourceResolver.adaptTo(Session.class);

			String failedWorkItemQuery = "SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE([/content/dam/we-retail/en]) and [jcr:content/cq:lastReplicated] is  null";
			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();
					String assetPath = nextNode.getPath();
					Map<String, Info> search = new ReferenceSearch().search(resourceResolver, assetPath, -1, 0);
					for (Map.Entry<String, Info> entry : search.entrySet()) {

						Info info = entry.getValue();
						for (String p : info.getProperties()) {
							String pagepath = info.getPage().getPath();
							slingHttpServletResponse.getWriter().println(assetPath.concat("***********").concat(pagepath));

						}

					}
				}
			}
		}

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

	}
}

 

/**
 * 
 */
package com.task.core.services;

import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;



/**
 * @author debal
 * 
 *         This service will be used as a utility and it will help us to get
 *         resource resolver object  and close resource resolver
 *
 */
public interface JcrUtility {

	public ResourceResolver getResourceResolver();

	public void closeResourceResolver(ResourceResolver resourceResolver);
	
	
}

 

/**
 * 
 */
package com.task.core.impl;

import java.util.HashMap;
import java.util.Map;
import java.util.Objects;

import javax.jcr.Session;

import org.apache.sling.api.resource.LoginException;
import org.apache.sling.api.resource.Resource;
import org.apache.sling.api.resource.ResourceResolver;
import org.apache.sling.api.resource.ResourceResolverFactory;
import org.osgi.service.component.annotations.Component;
import org.osgi.service.component.annotations.Reference;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.task.core.services.JcrUtility;

/**
 * @author debal
 *
 */
@Component(service = JcrUtility.class, immediate = true) 
public class JcrUtilityImpl implements JcrUtility {

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

	@Reference
	ResourceResolverFactory resourceResolverFactory;
	
	

	@Override
	public ResourceResolver getResourceResolver() {
		Map<String, Object> map = new HashMap<String, Object>();
		map.put(resourceResolverFactory.SUBSERVICE, "readWriteService");
		ResourceResolver serviceResourceResolver = null;
		try {

			serviceResourceResolver = resourceResolverFactory.getServiceResourceResolver(map);
		} catch (LoginException e) {
			logger.error("Could not get service user [ {} ]", "demoSystemUser", e.getMessage());
		}
		return serviceResourceResolver;

	}

	@Override
	public void closeResourceResolver(ResourceResolver resourceResolver) {
		if (Objects.nonNull(resourceResolver)) {
			resourceResolver.close();
		}

	}

	
}

 

 

I am using JCR_SQL2 query below to identify unpublished assets

SELECT * FROM [dam:Asset] AS s WHERE ISDESCENDANTNODE([/content/dam/we-retail/en]) and [jcr:content/cq:lastReplicated] is null 

Avatar

Community Advisor

Hi @Amir_khan4 

 

You can use ReferenceSearch API to get references of an asset. Below is the example sample:

 

      Collection<ReferenceSearch.Info> refs = new ReferenceSearch()
          .search(adminResolver, path).values();
      for (ReferenceSearch.Info info : refs) {
        String refPath = info.getPage().getPath();
      }


 Hope it helps!

Thanks,

Nupur