Expand my Community achievements bar.

SOLVED

JCR SQL2 Query or Query builder predicate

Avatar

Level 2

Need a jcr sql2 query for acs commons report or query builder predicate to get the list of pages which don't have specific cq:tags
Our tags hierarchy :

srikanth532_0-1651834585463.png

and the node structure is jcr:content/metadata/cq:tags

srikanth532_1-1651834715994.png

 

Here i want all the pages which do not have dsc:device or dsc:keywords tags

1 Accepted Solution

Avatar

Correct answer by
Level 7

I don't understand what your requirements exactly are. As far as I understand your prictures -whose shows your structure- the marked tags are a part of full path from others. So there is no other way. May be the keyword 'contains' allows you to avoid der percent signs. My above query would looks like:

SELECT * FROM [dam:AssetContent] AS page WHERE ISDESCENDANTNODE(page ,"/search/in/path") 
AND NOT (Contains(page.[cq:tags], 'dsc:keywords') or Contains(page.[cq:tags], 'dsc:device'))

 

The query builder pendant for my both queries is:

path=/content/dam/xxx
1_group.1_property=jcr:primaryType
1_group.1_property.value=dam:AssetContent
2_group.1_property=cq:tags
2_group.1_property.value=dsc:keywords%
2_group.1_property.operation=like
2_group.2_property=cq:tags
2_group.2_property.value=dsc:devices%
2_group.2_property.operation=like
2_group.p.or=true
2_group.p.not=true
orderby=path  

 

View solution in original post

3 Replies

Avatar

Level 7

 

SELECT * FROM [cq:PageContent] AS page WHERE ISDESCENDANTNODE(page ,"/search/in/path") 
AND NOT (page.[cq:tags] like "dsc:keywords%" or page.[cq:tags] like "dsc:device%")

 

Due to the fact that the information is saved in jcr:content node, you have to work with this resource.

Avatar

Level 2

I tried the above query in acs-commons it works fine but instead of giving directly like %dsc:device% we need to get that value from dropdown(search parameter). I tried like '{{tags}}' to get the value(dsc:device) from the dropdown but it didn't work.

srikanth532_0-1651850485399.png
if possible, can this be achieved in query builder predicate

 

Avatar

Correct answer by
Level 7

I don't understand what your requirements exactly are. As far as I understand your prictures -whose shows your structure- the marked tags are a part of full path from others. So there is no other way. May be the keyword 'contains' allows you to avoid der percent signs. My above query would looks like:

SELECT * FROM [dam:AssetContent] AS page WHERE ISDESCENDANTNODE(page ,"/search/in/path") 
AND NOT (Contains(page.[cq:tags], 'dsc:keywords') or Contains(page.[cq:tags], 'dsc:device'))

 

The query builder pendant for my both queries is:

path=/content/dam/xxx
1_group.1_property=jcr:primaryType
1_group.1_property.value=dam:AssetContent
2_group.1_property=cq:tags
2_group.1_property.value=dsc:keywords%
2_group.1_property.operation=like
2_group.2_property=cq:tags
2_group.2_property.value=dsc:devices%
2_group.2_property.operation=like
2_group.p.or=true
2_group.p.not=true
orderby=path