SQL2 Query with multiple path

[P_V_Nair]

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?

[ManviSharma · Accepted solution]

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.

[P_V_Nair]

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