Query to fetch assets with either offTime doesn't exists or offTime greater than current time. | Community
Skip to main content
jezwn
Level 5
October 17, 2024
Solved

Query to fetch assets with either offTime doesn't exists or offTime greater than current time.

  • October 17, 2024
  • 1 reply
  • 1250 views

Please see my query below.

p.limit=-1
p.offset=0
path=/content/dam
type=dam:Asset

1_property=jcr:content/cq:lastReplicationAction_scene7
1_property.value=Activate

 

2_property=jcr:content/cq:lastReplicationAction
2_property.value=Activate

3_group.or=true

3_group.1_property=jcr:content/metadata/custom:assetType

3_group.1_property.1_value=image

3_group.1_property.2_value=document

3_group.1_property.1.operation=equals

3_group.1_property.2.operation=equals

 

4_group.or=true

4_group.1_property.operation=not

4_group.1_property=jcr:content/offTime

4_group.2_daterange.property=jcr:content/offTime

4_group.2_daterange.operation=>=

4_group.2_daterange.lowerBound=1729147820649

 

orderby.sort=asc

orderby=@jcr:content/jcr:lastModified



The corresponding XPath generated is,

/jcr:root/content/dam//element(*, dam:Asset)
[
((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image'))
and (not(jcr:content/@8690475)
and (jcr:content/@8690475 > xs:dateTime('2024-10-17T06:50:20.649Z')))
and (jcr:content/@5644378:lastReplicationAction_scene7 = 'Activate' and jcr:content/@5644378:lastReplicationAction = 'Activate')
]
order by jcr:content/@6655266:lastModified

But what I'm looking is those assets where the offTime doesn't exist or offTime greater than current time, which should be an OR condition. But what I'm getting is an AND condition. How do I solve this?


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 MukeshYadav_

The first query returns in xpath as:

/jcr:root/content/dam//element(*, dam:Asset) [ ((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image')) and (not(jcr:content/@offTime) and (jcr:content/@offTime > xs:dateTime('2024-10-17T06:50:20.649Z'))) and (jcr:content/@cq:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cq:lastReplicationAction = 'Activate') ] order by jcr:content/@jcr:lastModified


And the second as,

/jcr:root/content/dam//element(*, dam:Asset) [ ((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image')) and (not(jcr:content/@offTime) and (jcr:content/@offTime > xs:dateTime('2024-10-17T06:50:20.649Z'))) and (jcr:content/@cq:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cq:lastReplicationAction = 'Activate') ] order by jcr:content/@jcr:lastModified


Please note that both are still AND condition.


Hi @jezwn ,

Please try below one

p.limit=-1 p.offset=0 path=/content/dam type=dam:Asset 1_property=jcr:content/cq:lastReplicationAction_scene7 1_property.value=Activate 2_property=jcr:content/cq:lastReplicationAction 2_property.value=Activate 3_group.p.or=true 3_group.1_property=jcr:content/metadata/custom:assetType 3_group.1_property.1_value=image 3_group.1_property.2_value=document 3_group.1_property.1.operation=equals 3_group.1_property.2.operation=equals 4_group.p.or=true 4_group.1_property.operation=exists 4_group.1_property=jcr:content/offTime 4_group.1_property.value=false 4_group.2_daterange.property=jcr:content/offTime 4_group.2_daterange.operation=>= 4_group.2_daterange.lowerBound=1729147820649 orderby.sort=asc orderby=@jcr:content/jcr:lastModified

Use group.p.or=true instead of group.or=true

Thanks

1 reply

MukeshYadav_
Community Advisor
Community Advisor
October 17, 2024

Hi @jezwn ,

You may try below xpath once,

/jcr:root/content/dam//element(*, dam:Asset) [ ((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image')) and ( not(jcr:content/@offtime) or (jcr:content/@offtime > xs:dateTime('2024-10-17T06:50:20.649Z')) ) and (jcr:content/@cQ:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cQ:lastReplicationAction = 'Activate') ] order by jcr:content/@jcr:lastModified

 

Thanks

jezwn
jezwnAuthor
Level 5
October 17, 2024

The xpath works correctly. But how do I do the same with querybuilder query.

jezwn
jezwnAuthor
Level 5
October 18, 2024

You may try below query with minor (order) changed-

 

p.limit=-1 p.offset=0 path=/content/dam type=dam:Asset 1_property=jcr:content/cq:lastReplicationAction_scene7 1_property.value=Activate 2_property=jcr:content/cq:lastReplicationAction 2_property.value=Activate 3_group.or=true 3_group.1_property=jcr:content/metadata/custom:assetType 3_group.1_property.1_value=image 3_group.1_property.2_value=document 3_group.1_property.1.operation=equals 3_group.1_property.2.operation=equals 4_group.or=true 4_group.1_property=jcr:content/offTime 4_group.1_property.operation=not 4_group.2_daterange.property=jcr:content/offTime 4_group.2_daterange.operation=>= 4_group.2_daterange.lowerBound=1729147820649 orderby.sort=asc orderby=@jcr:content/jcr:lastModified

 

If above works then fine else you may check exist with exists operation as false

 

p.limit=-1 p.offset=0 path=/content/dam type=dam:Asset 1_property=jcr:content/cq:lastReplicationAction_scene7 1_property.value=Activate 2_property=jcr:content/cq:lastReplicationAction 2_property.value=Activate 3_group.or=true 3_group.1_property=jcr:content/metadata/custom:assetType 3_group.1_property.1_value=image 3_group.1_property.2_value=document 3_group.1_property.1.operation=equals 3_group.1_property.2.operation=equals 4_group.or=true 4_group.1_property.operation=exists 4_group.1_property=jcr:content/offTime 4_group.1_property.value=false 4_group.2_daterange.property=jcr:content/offTime 4_group.2_daterange.operation=>= 4_group.2_daterange.lowerBound=1729147820649 orderby.sort=asc orderby=@jcr:content/jcr:lastModified

 

 

Thanks


The first query returns in xpath as:

/jcr:root/content/dam//element(*, dam:Asset) [ ((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image')) and (not(jcr:content/@offTime) and (jcr:content/@offTime > xs:dateTime('2024-10-17T06:50:20.649Z'))) and (jcr:content/@cq:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cq:lastReplicationAction = 'Activate') ] order by jcr:content/@jcr:lastModified


And the second as,

/jcr:root/content/dam//element(*, dam:Asset) [ ((jcr:content/metadata/@custom:assetType = 'document' or jcr:content/metadata/@custom:assetType = 'image')) and (not(jcr:content/@offTime) and (jcr:content/@offTime > xs:dateTime('2024-10-17T06:50:20.649Z'))) and (jcr:content/@cq:lastReplicationAction_scene7 = 'Activate' and jcr:content/@cq:lastReplicationAction = 'Activate') ] order by jcr:content/@jcr:lastModified


Please note that both are still AND condition.