Expand my Community achievements bar.

July 31st AEM Gems Webinar: Elevate your AEM development to master the integration of private GitHub repositories within AEM Cloud Manager.
SOLVED

SQL2 Query with multiple path

Avatar

Adobe Champion

I have the below query giving node traversal error. We have index in place for all the properties and using query manager we can see the right index is being picked also.

 

SELECT * FROM [dam:Asset] AS a WHERE (ISDESCENDANTNODE(a,[/content/dam/test/vendor/external])  OR ISDESCENDANTNODE(a,[/content/dam/test/team]) ) AND a.[jcr:content/notificationSent] = 'false' AND a.[jcr:content/dam:assetState] = 'processed' AND a.[jcr:content/jcr:lastModified] >= CAST('2023-05-21T18:30:00.000Z' AS DATE)

 

But the same query when ran with individual paths with out OR clause give results.

For eg:

SELECT * FROM [dam:Asset] AS a WHERE (ISDESCENDANTNODE(a,[/content/dam/test/vendor/external]) ) AND a.[jcr:content/notificationSent] = 'false' AND a.[jcr:content/dam:assetState] = 'processed' AND a.[jcr:content/jcr:lastModified] >= CAST('2023-05-21T18:30:00.000Z' AS DATE) ----> gives results

 

SELECT * FROM [dam:Asset] AS a WHERE ( ISDESCENDANTNODE(a,[/content/dam/test/team]) ) AND a.[jcr:content/notificationSent] = 'false' AND a.[jcr:content/dam:assetState] = 'processed' AND a.[jcr:content/jcr:lastModified] >= CAST('2023-05-21T18:30:00.000Z' AS DATE) -------> gives results

 

 

When i add both the paths in the same query with a OR clause, it gives node traversal error

 

Can someone help on what is causing this issue in the first query above?

 

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Hi,

 

The node traversal error in the first query with the OR clause is likely due to the limitation of performing complex OR queries involving multiple ISDESCENDANTNODE conditions. I believe AEM does not support this kind of query(correct me if I'm wrong )

 

To overcome this, consider using the UNION operator to combine the results of separate queries for each path with an ISDESCENDANTNODE condition. This approach can help avoid the node traversal error and retrieve the desired assets.

View solution in original post

2 Replies

Avatar

Correct answer by
Employee Advisor

Hi,

 

The node traversal error in the first query with the OR clause is likely due to the limitation of performing complex OR queries involving multiple ISDESCENDANTNODE conditions. I believe AEM does not support this kind of query(correct me if I'm wrong )

 

To overcome this, consider using the UNION operator to combine the results of separate queries for each path with an ISDESCENDANTNODE condition. This approach can help avoid the node traversal error and retrieve the desired assets.

Avatar

Adobe Champion

@ManviSharma The same query with OR clause works in our stage environment without any issues.