AEM Find Assets Referenced By Image Components

Question

I'm trying to find pages in AEM 6.1 that reference images from the DAM that are wider than 1280px. I'm really struggling to figure out if this is possible via a single JCR_SQL2 query or not. I've tried many things that have not worked (get ParseException when trying to query from CRXDE), but I think the following somewhat conveys what I'm after, except I think I'd need some additional joins, starting at cq:Page, to get pages instead of the actual image component nodes:

SELECT s.* from [nt:unstructured] as s
INNER JOIN [dam:Asset] as a on ISSAMENODE(a, s.[fileReference])
WHERE a.[jcr:content/metadata/tiff:ImageWidth] >= 1280

I've tried joining on jcr:path equality as well, but I can't get anything to actually run.

IT Gumby IT Gumby · Accepted Answer · 2016-02-25T02:54:28

I believe you do not need to join anything to achieve what you are looking for. I tested the following in crx-de lite "Tools.Query":

Finding assets:

SELECT * from [dam:Asset] as a
WHERE a.[jcr:content/metadata/tiff:ImageWidth] >= 1000

Finding references to an asset:

SELECT * FROM [nt:unstructured] AS r
WHERE r.fileReference = '/content/dam/my-images/my-icon.png'
AND ISDESCENDANTNODE(r, '/content')  // optimization to reduce query space

Most join examples revolve around parent-child relationships ISCHILDNODE(parent, child), which don't apply to this use case. Unfortunately, joining the String r.fileReference to Resource a has been beyond my research so far.

The closest example I could find was from #6 on http://labs.6dglobal.com/blog/2014-10-07/9-jcr-sql-2-queries-every-aem-dev-should-know/

SELECT parent.* FROM [cq:Page] AS parent
INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent)
WHERE ISDESCENDANTNODE(parent, '/content') AND child.[cq:template] = '/libs/cq/personalization/templates/campaign'

AEM Find Assets Referenced By Image Components

1 Answers