ACS Commons Reports: Reference report: Querying and Date Comparison | Community
Skip to main content
bona-2
bona-2
Level 2
July 31, 2024

ACS Commons Reports: Reference report: Querying and Date Comparison

  • July 31, 2024
  • 3 replies
  • 2154 views

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

This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

3 replies

bona-2
bona-2Author
Level 2
August 2, 2024

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
    Community Advisor and Adobe Champion
    EstebanBustamanteCommunity Advisor and Adobe Champion
    Community Advisor and Adobe Champion
    August 2, 2024

    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/commons/util/AssetReferenceSearch.html, 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-all-images-used-in-content-pages/m-p/414668 

     

    Hope this helps.

    Esteban Bustamante
      bona-2
      bona-2Author
      Level 2
      September 5, 2024

      In JCR-SQL2, comparing dates for this case directly is not possible because `cq:lastModified` and `cq:lastReplicated` are dynamic operands.

       

      However, the QueryBuilder API allows date comparisons using the `daterange` keyword, as documented here: Query builder cheatsheet

       

      For reference searches, we rely on the `fulltext` or 'contains' property, but it has a limitation: it can only search up to five levels deep within the node hierarchy, and some assets are nested beyond that level, but this case won't exist all the time.

       

      While the JCR-SQL2 query I mentioned does return the expected results, its execution time is significantly high, which can potentially impact system performance. Long-running queries like this can create additional load on the system, leading to performance degradation.

      Feel free to correct this statement....much appreciated.

      kautuk_sahni
      Community Manager
      kautuk_sahniCommunity Manager
      Community Manager
      August 7, 2024

      @bona-2 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!

      Kautuk Sahni
      Terms & ConditionsAccessibility statement

      Loading footer...