Expand my Community achievements bar.

SOLVED

QueryBuilder - multiple daterange properties

Avatar

Level 3

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

 

Topics

Topics help categorize Community content and increase your ability to discover relevant content.

1 Accepted Solution

Avatar

Correct answer by
Community Advisor

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!

View solution in original post

13 Replies

Avatar

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
@cQ: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 @jcr: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
@jcr: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 @jcr:created <= xs:dateTime('2015-11-03T20:00:00.000-05:00')) ]

 

Avatar

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.

 

 

Avatar

Community Advisor

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!

Avatar

Correct answer by
Community Advisor

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!

Avatar

Community Advisor

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!

Avatar

Level 3

it is not issue.I was just looking better way to do it instead of putting down all properties.

I think i do not have any other option.

Avatar

Community Advisor

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!

Avatar

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.

Avatar

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

Avatar

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

Avatar

Community Advisor

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