ACS Commons Reports: Reference report: Querying and Date Comparison

[bona]

I'm working on creating a reference report using the ACS Commons Reports tool in AEM.

The goal of this report is to allow users to enter an asset path and generate a list of pages where the asset is used. The report includes columns such as the containing page, modified date, and replicated date.

However, I've encountered an issue where the queries (like "contains" or "fulltext") do not search through grandchild nodes, potentially leading to incomplete results. Additionally, I need to ensure that the Page's modified date is more recent than the replicated date.

Could you provide guidance on how to write effective queries for this purpose or suggest alternative approaches to achieve accurate and comprehensive results?

Thank you

[bona]

Was able to do 50% of the above requirement.

SELECT parent.*
FROM [cq:Page] AS parent
INNER JOIN [nt:base] AS child
ON ISDESCENDANTNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, '/content/project/page-path')
AND (CONTAINS(child.*, '/content/dam/project/asset-path'))

Adding query for date will be pretty much helpful....

OR you can also share as an x-path

[EstebanBustamante]

Hi, 

I think the easiest way to achieve what you need is to use the Java API AssetReferenceSearch https://developer.adobe.com/experience-manager/reference-materials/6-4/javadoc/com/day/cq/dam/common... where you can get a list of pages and then you can extract from them any other information you need. 

Please check this thread, where also the query option is discussed: https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/how-to-find-the-list-of-al... 

Hope this helps.

[bona]

Thank you for your response, @EstebanBustamante.

We are leveraging the existing features provided by ACS Commons Reports, such as returning assets with simple queries and adding columns via components. Therefore, we don't want to create a new feature from scratch.

I have observed that in JCR-SQL2, it's not possible to compare dynamic operands like 'lastModified' and 'lastReplicated'. However, since QueryBuilder allows for date comparisons, I was hoping to modify the SQL2 query to suit QueryBuilder's syntax and capabilities. Could you help adjust the above JCR SQL 2 query accordingly to leverage QueryBuilder's ability to compare dates?

[EstebanBustamante]

Can you try using "cast" https://stackoverflow.com/questions/15117075/jcr-sql2-query-comparing-dates ?

[kautuk_sahni]

@bona Did you find the suggestions from users helpful? Please let us know if you require more information. Otherwise, please mark the answer as correct for posterity. If you've discovered a solution yourself, we would appreciate it if you could share it with the community. Thank you!