Query Builder

[Siva_Sogalapalli]

Hi,

I have a requirement to write a query to meet following:

Page has following properties:
startdate (i.e, page will be available from this date)
expirydate (i.e, page will be available till this date only)

Query has to list out all pages which are available in a period of time.

For examples:
Page1 
     startdate: 2014-07-01
     expirydate : 2014-08-30
Page2 
     startdate: 2014-07-01
     expirydate : 2014-08-30
Page3 
     startdate: 2014-09-01
     expirydate : 2014-09-30

Now I want to list all the pages which are available between 2014-07-15 to 2014-07-25. 

I've tried this but didn't meet my requirement:

type=cq:Page
group.p.or=true
group.1_daterange.property=jcr:content/startdate
group.1_daterange.lowerBound=2014-07-15
group.1_daterange.upperBound=2014-07-25
group.2_daterange.property=jcr:content/expirydate
group.2_daterange.lowerBound=2014-07-15
group.2_daterange.upperBound=2014-07-25

Quick response would be helpful.

Thanks

Siva

[VatsalGupta]

Hi Siva,

Can you clarify if the requirement is to-

a) List the pages for which both start and expiry date fall in the given period?

OR

b) List the pages which are available even partially in the given period? (e.g. start date is 2014-07-13 and expiry date is 2014-07-20)

Please clarify so that correct solution can be suggested.

Thanks

[JitendraBansal]

Hi,

There are problems in query map above :

First of all the date should be in ISO format (yyyy-MM-dd'T'HH:mm:ss.SSS). This is how AEM stores the date values at jcr nodes.

Second for daterange predicate upper bound and lower bound operators are not specified.

Third relative path of the node where values for start date and end date are stored seems incorrect.

So your map should look as shown below:

            map.put( "group.1_daterange.property" , "jcr:content/compNodeWhereValueExists/startDate");
            map.put( "group.1_daterange.upperBound", "2014-08-26T00:00:00.000+02:00");
            map.put( "group.1_daterange.upperOperation" , "<=");
            
            map.put( "group.2_daterange.property" , "jcr:content/compNodeWhereValueExists/endDate");
            map.put( "group.2_daterange.lowerBound" , "2013-12-31T00:00:00.000+02:00");
            map.put( "group.2_daterange.lowerOperation" , ">=");

[Siva_Sogalapalli]

Thanks for your replies.

Gupta, Second one is the requirement

b) List the pages which are available even partially in the given period? (e.g. start date is 2014-07-13 and expiry date is 2014-07-20)

Example:

Page start date is 2014-07-13 and expiry date is  2014-07-30

Case 1: In the query if we use start date is 2014-07-10 and expiry date is 2014-07-20, it should display the result as the page is available in between 2014-07-10 , 2014-07-20,  

Case 2: In the query if we use start date is 2014-07-05 and expiry date is 2014-08-10, also it should display the result as the page start date fall in 2014-07-05 , 2014-08-10

Case 3: In the query if we use start date is 2014-07-15 and expiry date is 2014-08-10, also it should display the result as the page end date fall in 2014-07-15 , 2014-08-10

[Runal_Trivedi · Accepted solution]

Hi Siva,

Based on the condition and criteria you mentioned, I guess below query would suffice your need.

Using querybuilder json it would be as below:

http://localhost:4502/bin/querybuilder.json?type=cq:Page&group.p.or=false&group.1_daterange.property...

If you want to go via API then it would be as below:

            map.put( "group.1_daterange.property" , "jcr:content/compNodeWhereValueExists/startDate");
            map.put( "group.1_daterange.upperBound", "2014-07-30T00:00:00.000+02:00");
            map.put( "group.1_daterange.upperOperation" , "<=");
            
            map.put( "group.2_daterange.property" , "jcr:content/compNodeWhereValueExists/endDate");
            map.put( "group.2_daterange.lowerBound" , "2014-07-13T00:00:00.000+02:00");
            map.put( "group.2_daterange.lowerOperation" , ">=");
            
Only thing assumed here is startdate will always be less than or equal to expirydate.

Basically the query has condition - type=page & (startdate<=2014-07-30 & expirydate>=2014-07-13)

Hope it helps

Thanks

Runal

[Siva_Sogalapalli]

Hi Runal,

Still I've a failure case:

For example: If the Page start date is 2014-08-05 and expiry date is  2014-08-30.

If you use Query as: 

type=cq:Page
group.p.or=true
group.1_daterange.property=jcr:content/fstartDate
group.1_daterange.upperBound=2014-07-15T00:00:00
group.1_daterange.upperOperation = "<="
group.2_daterange.property=jcr:content/endDate
group.2_daterange.lowerBound=2014-07-01T00:00:00
group.2_daterange.lowerOperation = ">="

This query will display the page as endDate is greater than Lower Bound. But it will no fall into search option. Please advise me if you have any other.

[Runal_Trivedi]

Hi Siva,

The issue with query that you have mentioned is; group.p.or is set to true whereas if you observe the querybuilder.json I pasted, it contains group.p.or=false, if it is set to false then it means you are putting & conditions between your criterias and it should work then.

Right now in query for data you mentioned, condition for startdate does not match but condition for expiry date matches and since group.p.or is set to true (which is OR Condition) hence the overall condition becomes true and the page is coming up in search result.

Regards

Runal