Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

SOLVED

Create Index for OOTB query with "order by lower(name(a)) desc"

Avatar

Level 4

Hello everyone, 

i'm working with a big assets project, where the client has some folders with thousands of assets, when it tries to review the folder content and sort by name for example, I'm seeing a warn message log due to traversal query.

Basically it is the list view and then sort by name.

 

The query is

SQL2: select [jcr:path], [jcr:score], * from [nt:base] as a where ischildnode(a, '/content/dam/folder') order by lower(name(a)) desc

xpath: /jcr:root/content/folder/* order by fn:lower-case(fn:name()) descending

 

I tried to create a new index under nt:base with 

 

nt:base

...properties

......name
.........name = "function*lower*@:name"
.........ordered = true
.........type = "String"

 

but it doesn't work, i mean i generated the index then used the query performance tool, but the index is not used and I still see the message in the log.

 

any help is welcome!

1 Accepted Solution

Avatar

Correct answer by
Level 4

Finally i have the solution, below the index that is working fine

 

victor_toledo_3_0-1649168038129.png

victor_toledo_3_1-1649168050708.png

victor_toledo_3_2-1649168061079.png

the includedPath and evaluatePathRestrictions were the key

View solution in original post

0 Replies

Avatar

Community Advisor

Please refer the detail discussion here :

Solved: Re: Oak Indexing : Traversal Warning : AEM 6.5.10 - Adobe Experience League Community - 4464...

 

One more point instead of putting thousands of assets in lesser number of folders , you could reorganize your AEM folder structure with multiple subfolders based category like products or brands. 

 

But before reorganize, we need to review our backend logic incase if we have defined any path like /content/dam/<project-name>/brands in Java classes, you need to inform stakeholders also.

Avatar

Level 4

Thank you for you quick response.

yes, that was my first recommendation, i mean create subfolders, but those are special folders for them and they need it without subfolders. 

this is OOTB functionality it is the code that is executed when you try to sort assets by name.

Avatar

Community Advisor

In that resolved ticket you could see we all faced this type of issue while performing the activity like opening the DAM folder, asset move operation. 

 

To fix the issue we have raised the day care ticket with adobe  and made necessary change as per Adobe's input.

 

Hope this will help.

Avatar

Level 4

I think it is not the same problem, here the index must go through the order by clause, it is not the same situation. For me it is not the correct answer. And increasing the limit on memory can be problematic

@kautuk_sahni i think this is not the correct answer. I'm asking about the index and not a workaround

Avatar

Community Advisor

@victor_toledo_3 ,

 

Please accept my apology for the inconvenience.

I can understand the problem is different here but I was talking about the approach. 

 

All of us encountered  traversal query issue with different activities like accessing of DAM folder, asset move operation.

 

So we raised day care/support ticket with Adobe and made necessary change as per Adobe's input.

Avatar

Correct answer by
Level 4

Finally i have the solution, below the index that is working fine

 

victor_toledo_3_0-1649168038129.png

victor_toledo_3_1-1649168050708.png

victor_toledo_3_2-1649168061079.png

the includedPath and evaluatePathRestrictions were the key