Level 3

Solved

QueryBuilder - multiple daterange properties

Forum|Forum|5 years ago
June 2, 2020
7 replies
9448 views

Hi,

Is it possible to give a regex pattern for a daterange.property in aem query builder and search for all properties falling under the regex pattern.

Usecase : we are trying to query around 30 date properties set for an asset whether if any particular date among them falls under the given date range.

Thanks,

Deepikaa

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 Nupur_Jain

Hi @deepikaa_nagesh

It is not possible to apply datarange property name as regex. You will have to use group to specify multiple properties like

group.1_daterange.property=date1
group.1_daterange.lowerBound=2020-01-01
group.1_daterange.upperBound=2020-02-20

group.2_daterange.property=date2
group.2_daterange.lowerBound=2020-01-01
group.2_daterange.upperBound=2020-02-20

group.p.or=true

Change this group.p.or=true/false dependding upon if you want all of them to be in specific range or any one.

I know this will create a long query but if you are using query builder in Java, you can loop on array of date property names and creates the predicate conditions like:

String datePropertyArray = ["date1", "date2", "date3", "date4"];

for(int number = 0; number < datePropertArray.length(); number++) {

     map.put("group" + number + "_daterange.property", datePropertyArray[number]);
     map.put("group" + number + "_daterange.lowerBound", "2020-01-01");
     map.put("group" + number + "_daterange.upperBound", "2020-02-20");

}

Hope it helps!

L

leoberliant

Adobe Employee

Check if this sample helps:

path=/content
type=cq:PageContent
group.p.or=true
group.1_daterange.property=cq:lastModified
group.1_daterange.upperBound=2017-04-21T23:59:59.000-04:00
group.1_daterange.lowerBound=2017-04-21T00:00:00.000-04:00

group.2_daterange.property=jcr:created
group.2_daterange.upperBound=2017-04-21T23:59:59.000-04:00
group.2_daterange.lowerBound=2017-04-21T00:00:00.000-04:00
p.limit=-1

/jcr:root/content//element(*,cq:PageContent)[(@cq:lastModified >= xs:dateTime('2015-11-03T04:00:00.000-05:00') and
@5644378:lastModified <= xs:dateTime('2015-11-03T20:00:00.000-05:00')) or (@jcr:created >= xs:dateTime('2015-11-03T04:00:00.000-05:00')
and @6655266:created <= xs:dateTime('2015-11-03T20:00:00.000-05:00')) ]

/jcr:root/content//element(*,dam:AssetContent)[(@jcr:lastModified >= xs:dateTime('2015-11-03T04:00:00.000-05:00') and
@6655266:lastModified <= xs:dateTime('2015-11-03T20:00:00.000-05:00')) or (@jcr:created >= xs:dateTime('2015-11-03T04:00:00.000-05:00')
and @6655266:created <= xs:dateTime('2015-11-03T20:00:00.000-05:00')) ]

Deepikaa_NageshAuthor

Level 3

Hi,

Thanks for replying.

I have around 30 properties ,, i wanted to check if i can write regex pattern for a property rather than listing everything one by one.

Nupur_Jain

Adobe Employee

Hi @deepikaa_nagesh

It is not possible to apply datarange property name as regex. You will have to use group to specify multiple properties like

group.1_daterange.property=date1
group.1_daterange.lowerBound=2020-01-01
group.1_daterange.upperBound=2020-02-20

group.2_daterange.property=date2
group.2_daterange.lowerBound=2020-01-01
group.2_daterange.upperBound=2020-02-20

group.p.or=true

Change this group.p.or=true/false dependding upon if you want all of them to be in specific range or any one.

I know this will create a long query but if you are using query builder in Java, you can loop on array of date property names and creates the predicate conditions like:

String datePropertyArray = ["date1", "date2", "date3", "date4"];

for(int number = 0; number < datePropertArray.length(); number++) {

     map.put("group" + number + "_daterange.property", datePropertyArray[number]);
     map.put("group" + number + "_daterange.lowerBound", "2020-01-01");
     map.put("group" + number + "_daterange.upperBound", "2020-02-20");

}

Hope it helps!

Nupur_Jain

Accepted solution

Adobe Employee

Hi @deepikaa_nagesh

It is not possible to apply datarange property name as regex. You will have to use group to specify multiple properties like

group.1_daterange.property=date1
group.1_daterange.lowerBound=2020-01-01
group.1_daterange.upperBound=2020-02-20

group.2_daterange.property=date2
group.2_daterange.lowerBound=2020-01-01
group.2_daterange.upperBound=2020-02-20

group.p.or=true

Change this group.p.or=true/false dependding upon if you want all of them to be in specific range or any one.

I know this will create a long query but if you are using query builder in Java, you can loop on array of date property names and creates the predicate conditions like:

String datePropertyArray = ["date1", "date2", "date3", "date4"];

for(int number = 0; number < datePropertArray.length(); number++) {

     map.put("group" + number + "_daterange.property", datePropertyArray[number]);
     map.put("group" + number + "_daterange.lowerBound", "2020-01-01");
     map.put("group" + number + "_daterange.upperBound", "2020-02-20");

}

Hope it helps!

Nupur_Jain

Adobe Employee

Hi @deepikaa_nagesh

It is not possible to apply datarange property name as regex. You will have to use group to specify multiple properties like

group.1_daterange.property=date1
group.1_daterange.lowerBound=2020-01-01
group.1_daterange.upperBound=2020-02-20

group.2_daterange.property=date2
group.2_daterange.lowerBound=2020-01-01
group.2_daterange.upperBound=2020-02-20

group.p.or=true

Change this group.p.or=true/false dependding upon if you want all of them to be in specific range or any one.

I know this will create a long query but if you are using query builder in Java, you can loop on array of date property names and creates the predicate conditions like

String[] datePropertyArray = {"date1", "date2", "date3", "date4"};

for(int number = 0; number < datePropertyArray.length; number++) {

     map.put("group" + number + "_daterange.property", datePropertyArray[number]);
     map.put("group" + number + "_daterange.lowerBound", "2020-01-01");
     map.put("group" + number + "_daterange.upperBound", "2020-02-20");

}

Hope it helps!

Nupur_Jain

Adobe Employee

@deepikaa_nagesh

were you able to solve your issue?

Nupur_Jain

Adobe Employee

Hi @deepikaa_nagesh

you can not use regex in daterange property, you will have to group multiple dates and apply OR or AND to it. If you are using query builder in Java, you can loop on array of date property names and creates the conditions like:

String[] datePropertyArray = {"date1", "date2", "date3", "date4"};

for(int number = 0; number < datePropertyArray.length; number++) {
map.put("group" + number + "_daterange.property", datePropertyArray[number]);
map.put("group" + number + "_daterange.lowerBound", "2020-01-01");
map.put("group" + number + "_daterange.upperBound", "2020-02-20");
}

Hope it helps!

Deepikaa_NageshAuthor

Level 3

Hi,

thank you for replying.

i have around 30 properties like expiryDate_us , expiryDate_UK..so on for 30 countries. I wanted to check if there is any other way of doing it as i wanted to avoid putting entire list there.

Ankur_Khare

Community Advisor

Did u check this- using groovy script

daterange : This predicate is used to search a date property range.
- daterange.property : Specify a property which is searched.
- daterange.lowerBound : Fix a lower bound eg. 2010-07-25
- daterange.lowerOperation : “>” (default) or “>=”
- daterange.upperBound: Fix a lower bound eg. 2013-07-26
- daterange.upperOperation: “<” (default) or “<=”
relativedaterange: It is an extension of daterange which uses relative offsets to server time. It also supports 1s 2m 3h 4d 5w 6M 7y
- relativedaterange.lowerBound : Lower bound offset, default=0
- relativedaterange.upperBound : Upper bound Offset .

path=/home/users type=rep:User
group.1_daterange.property=jcr:created group.1_daterange.lowerBound=2014-08-18 group.1_daterange.upperBound=2014-08-19
group.2_daterange.property=cq:lastModified group.2_daterange.lowerBound=2014-08-18 group.2_daterange.upperBound=2014-08-19
group.p.or=true

Deepikaa_NageshAuthor

Level 3

Hi Ankur ,

What i am looking for is to handle multiple properties more than 30 without mentioning each property one by one.

I was trying to see something like i can use regex and entire list might be searched upon. or write a custom predicate to do this.

Thanks,

Deepikaa

VeenaVikraman

Community Advisor

We don't have a regex pattern for a daterange.property but you might have to group it and run the query.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded