I am trying to find the query to extract all the pages which has two components in it. I wrote the query to extract a single component but having trouble to write the query to pull the pages which has both components.
Any help would be appreciated.
Do you plan to use this query in source code or run it via /crx/de? Based on the use case, it would be easy to figure out if you need SQL2, XPATH or you could use QB etc.
How deep in the hierarchy level these components are configured in node structure? It's easy to look at just 2 node levels but if you want to go deep then it could become slightly complex and you might have to use APIs rather than native queries.
I am planning to execute it via crx/de. I don't think it makes much difference.
Our hierarchy is very deep probably 10 levels deep.
I don't want to write a program since that requires a code deployment to get the results. I am planning to execute the query in crx/de.
Here is my SQL2 query:
SELECT parent.* FROM [nt:unstructured] AS parent INNER JOIN [nt:base] AS child ON ISDESCENDANTNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, '/content/ABC/en-us') AND (parent.[sling:resourceType] = "abc/components/structure/templateA" AND child.[sling:resourceType]='abc/components/content/componentA')
I would like to include componentB in the query as well so I can get the pages have both component A&B.
I am able to get OR working but for some reason AND is not .. will try and update if I find something
The query to search 10 levels depth would be a big query and might break if it traverses more than 10k nodes.
In my opinion, it would be better to generate the query using QueryBuilder and use it however you would want to
Couple of options:
SQL2 query at same level -
SELECT * FROM [cq:PageContent] AS s WHERE ISDESCENDANTNODE(s,'/content/<project_root>) AND CONTAINS(s.[sling:resourceType], '<resource_type>')
SQL2 query for immediate child -
SELECT parent.* FROM [cq:Page] AS parent INNER JOIN [nt:base] AS child ON ISCHILDNODE(child,parent) WHERE ISDESCENDANTNODE(parent, '/content') AND child.[sling:resourceType] = 'wcm/foundation/components/responsivegrid'
Node API - Use Node.getNodes() with Iterator to traverse each node and find the property -
Use QueryBuilder to specify the depth as you mentioned -
property.depth : The number of additional levels to search under a node. eg. if property.depth=2 then the property is searched under
(@jcr:title = 'foo'or */@jcr:title = 'foo'or */*/@jcr:title = 'foo')
You could also create xpath query for traversing deep levels like mentioned below -
JCR 1.0: 22.214.171.124 Path Constraint (Content Repository for Java Technology API) - this is JCR v1.0, it should work..