How to combine two queries in AEM?

[Anderson_Hamer]

I am trying to get a node based on 2 conditions,
Condition number 1 - It should be /apps/myproject/templates/full-content-page template
Conditionnumber 2 - It should have property as cq:headerVal and value should be "col3"

I have created a query for these 2  conditions. Below query will be meet 1st condition

path=/content/myproject/sites/en-us
1_property=jcr:primaryType
1_property.value=cq:PageContent
2_property=cq:template
2_property.value=/apps/myproject/templates/full-content-page

This query is working for second condition

path=/content/myproject/sites/en-us
1_property=jcr:primaryType
1_property.value=nt:unstructured
2_property=cq:headerVal
2_property.value=col3

But it is not working when i combine these 2 queries and run

path=/content/myproject/sites/en-us
1_property=jcr:primaryType
1_property.value=cq:PageContent
2_property=cq:template
2_property.value=/apps/myproject/templates/full-content-page
3_property=jcr:primaryType
3_property.value=nt:unstructured
4_property=cq:headerVal
5_property.value=col3

I can understand  why it is not working. it is trying to find both cq:template and cq:headerVal on same node but it is actually two different type of node.  Is there any way we can merge these two queries ?

[Kishore_Kumar_]

hi @Anderson_Hamer ,

Can you please try with this

type=cq:Page
path=/content/myproject/sites/en-us
1_property=jcr:content/cq:template
1_property.value=/apps/myproject/templates/full-content-page
2_property=jcr:content/*/cq:headerVal
2_property.value=col3
path.self=true
p.limit=-1

[Anderson_Hamer]

sorry, zero result. I think 

jcr:content/*/cq:headerVal is not working 

[Anderson_Hamer]

Please find below xpath 

//element(*, cq:Page)
[
(jcr:content/@cq:template = '/apps/myproject/templates/full-content-page' and jcr:content/_x002a_/@cq:headerVal = 'col3')
]

jcr:content/*/ became "jcr:content/_x002a_/" I think * is parsed as "_x002a_". I tried escape this * but no luck. can you tell if there any we can manage this?

[Anderson_Hamer]

Seems regex is not supported - https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/regex-in-querybuilder/td-p...

[Kishore_Kumar_]

Regex is not supported fully though, it supports wild card characters still and above query is working for only one depth. If you know the depth of 2nd property we can use like below.

type=cq:Page
path=/content/myproject/sites/en-us
1_property=jcr:content/cq:template
1_property.value=/apps/myproject/templates/full-content-page
2_property=cq:headerVal
2_property.value=col3
2_property.depth=5
path.self=true
p.limit=-1

[Anderson_Hamer]

Kishore - This dept "2_property.depth=5" works thanks. But it is returning the pageContent(page). I need to see the node where cq:headerVal lives. Can you tell me why it is not retuning node?

[Kishore_Kumar_]

Anderson - I thought you just need page path so i queried with cq:page, if you want to see the node where cq:headerVal lives, pls go ahead with @rampai solution. 

[Shubham_borole]

Hi,

Can you try this

path=/content/myproject/sites/en-us
1_group.1_property=jcr:primaryType
1_group.1_property.value=cq:PageContent
1_group.2_property=cq:template
1_group.2_property.value=/apps/myproject/templates/full-content-page
1_group.p.and=true
2_group.3_property=jcr:primaryType
2_group.3_property.value=nt:unstructured
2_group.4_property=cq:headerVal
2_group.4_property.value=col3
2_group.p.and=true

Also in your final query I see a 5_ in the last line, should it be 4_? Can you try below too?

path=/content/myproject/sites/en-us
1_property=jcr:primaryType
1_property.value=cq:PageContent
2_property=cq:template
2_property.value=/apps/myproject/templates/full-content-page
3_property=jcr:primaryType
3_property.value=nt:unstructured
4_property=cq:headerVal
4_property.value=col3

[Anderson_Hamer]

Sorry, zero result. This groping is not helping.

[rampai · Accepted solution]

Hi @Anderson_Hamer ,

You may need to use JCR_SQL2 for this use case.

To get the node:

SELECT child.* FROM [nt:unstructured] as child
INNER JOIN [cq:PageContent] as parent
ON ISDESCENDANTNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, '/content/myproject/sites/en-us')
AND child.[cq:headerVal] = 'col3'
AND parent.[cq:template] ='/apps/myproject/templates/full-content-page'

or to get the pageContent:

SELECT parent.* FROM [nt:unstructured] as child
INNER JOIN [cq:PageContent] as parent
ON ISDESCENDANTNODE(child, parent)
WHERE ISDESCENDANTNODE(parent, '/content/myproject/sites/en-us')
AND child.[cq:headerVal] = 'col3'
AND parent.[cq:template] ='/apps/myproject/templates/full-content-page' 

Thanks,

Ram

[Anderson_Hamer]

Great. Thank you Ram. It works (y). Can we do index if in case of sql2 query?

[rampai]

Yes @Anderson_Hamer. You can use the oakutils to generate the custom oakindex.