1

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.

David Hosier
  • 243
  • 3
  • 12

1 Answers1

1

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'
IT Gumby
  • 1,067
  • 8
  • 11
  • Right, the important part of the question is the "referenced by image components" part, not the size part. – David Hosier Feb 25 '16 at 17:29
  • Oops, missed that. Will take another look. – IT Gumby Feb 25 '16 at 20:04
  • I haven't had any luck solving this - the JOIN is defeating me. I can point out that `s.fileReference` is a String (NOT a Node/Resource), thus will fail on `ISSAMENODE()`. The only suggestion I can provide is querying for all the assets matching your criteria, then looping for subsequent queries to find any `fileReference` matching the path of each hit. – IT Gumby Feb 25 '16 at 22:45