New Participant

Solved

query regarding dam asset publish status

Forum|Forum|3 years ago
January 25, 2023
5 replies
2160 views

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-1 @arunpatidar @lukasz-m @debal_das

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 DEBAL_DAS

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;

/**
 * @380 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;



/**
 * @380 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;

/**
 * @380 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

Nupur_Jain

Employee

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

DEBAL_DASAccepted solution

New Participant

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;

/**
 * @380 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;



/**
 * @380 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;

/**
 * @380 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

Kiran_Vedantam

New Participant

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.

A

Amir_khan4Author

New Participant

Hi @kiran_vedantam

I need to check whether the asset is referenced anywhere.

Kiran_Vedantam

New Participant

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.

Mani_kumar_

New Participant

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.

Magicr

New Participant

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.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded