Fetching folder paths in ACS Reports

[Asritha]

Hi Team,

I want to fetch all folder paths under /content/dam/project of primaryType sling:Folder and nt:folder.

I have generated a custom ACS Commons Reports, in which I used this query SELECT * FROM [sling:Folder] AS folder WHERE ISDESCENDANTNODE('/content/dam/project');

I am not getting the fully generated report with this query, some folders are not included in the report which has primaryType as sling:Folder. So to avoid that, is there a way to set depth level of 7 on the result?

for example: we have paths like "/content/dam/project/level1/level2/level3/level4/level5/level6/level7", And I want all paths with this depth "/content/dam/project/level1/level2/level3/level4" and all folders under level 3 like "/content/dam/project/level1/level2/level3/a1",  "/content/dam/project/level1/level2/level3/a2" 

Any help through this is welcome.
Thanks.

@arunpatidar  @lukasz-m  @kautuk_sahni 

@SivakumarKanoori @Fanindra_Surat @Jagadeesh_Prakash @krati_garg @iamnjain @somen-sarkar @bajancherry @sunil_kumar_ @shelly-goel @Ganthimathi_R @davidjgonzalezzzz @krishna_garikapati  @Shubham_borole @milind_bachani @krishna_sai @manjunathdj  

[EstebanBustamante]

Try the below query:

SELECT * FROM [nt:folder] AS folder
WHERE ISDESCENDANTNODE('/content/dam/project')
AND (folder.[jcr:primaryType] = 'sling:Folder' OR folder.[jcr:primaryType] = 'nt:folder')

This query selects all nodes of type "nt:folder" under "/content/dam/project" and filters them based on their primary type to include only those with "sling:Folder" and "nt:folder" as their primary types.

[Asritha]

Hey,

I wanted sling:Folder as primaryType and I was looking for a way to add depth of 7 on the folder paths.

[aanchal-sikka · Accepted solution]

Hello @Asritha 

You have mentioned that few sling:Folder are not listed. Can you please verify once, if your query is using a proper index.

If not, you might be receiving inconsistent results. 

Please try creating an index and verify the results.

Also, there is a predicate in querybuilder.

• property.depth : The number of additional levels to search under a node. eg. if property.depth=3 then the property is searched till 3 level from base node. It is mostly used for nested search.

You can execute and check for its SQL equivalent in query logs