How to filter SQL2 query based on a property in Parent node (2nd level) | Community
Skip to main content
M
MrudulMo
July 25, 2023
Solved

How to filter SQL2 query based on a property in Parent node (2nd level)

  • July 25, 2023
  • 2 replies
  • 854 views

Hi,

I have a query which returns the dam:Asset nodes. However, I have to add a clause to filter out further based on the property in 2nd level of parent node (sling:Folder)

Present Query looks like this: SELECT PATH FROM [dam:Asset] AS a WHERE ISDESCENDANTNODE(a,[/content/dam/my-org/testdev25ex])

The result set of above should be further filtered by checking a jcr property in 2nd level of parent folder which is always a sling:Folder
I can always work on results and filter out programmatically. However, i want to apply this filter in the query itself.

Anyone have idea how can we achieve this ?

Node structure would look like this:

Marked images in the green should basically filtered further based on the property in second level of parent node which is testdev25ex 

 

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

Hi ,

 

To filter based on a property in the 2nd level of the parent node (sling:Folder), use the JOIN clause in your query:

 

SELECT asset.[jcr:path] FROM [dam:Asset] AS asset
JOIN [sling:Folder] AS folder ON ISCHILDNODE(asset, folder)
WHERE ISDESCENDANTNODE(asset, [/content/dam/my-org/testdev25ex])
AND folder.[propertyName] = 'desiredValue'
 

2 replies

BrianKasingli
Community Advisor and Adobe Champion
BrianKasingliCommunity Advisor and Adobe Champion
Community Advisor and Adobe Champion
July 26, 2023

I have not tested this, but would this work?

SELECT asset.[jcr:path] FROM [dam:Asset] AS asset JOIN [nt:base] AS parentFolder ON ISCHILDNODE(asset, parentFolder) WHERE ISDESCENDANTNODE(asset, '/content/dam/my-org/testdev25ex') AND parentFolder.[sling:resourceType] = 'sling:Folder' AND parentFolder.[yourProperty] = 'Your Value'
    ManviSharma
    Adobe Employee
    ManviSharmaAdobe EmployeeAccepted solution
    Adobe Employee
    July 26, 2023

    Hi ,

     

    To filter based on a property in the 2nd level of the parent node (sling:Folder), use the JOIN clause in your query:

     

    SELECT asset.[jcr:path] FROM [dam:Asset] AS asset
    JOIN [sling:Folder] AS folder ON ISCHILDNODE(asset, folder)
    WHERE ISDESCENDANTNODE(asset, [/content/dam/my-org/testdev25ex])
    AND folder.[propertyName] = 'desiredValue'
     
      Terms & ConditionsAccessibility statement

      Loading footer...