Expand my Community achievements bar.

SOLVED

Fetch Content from AEM between two dates using Query

Avatar

Level 2

Hi Community,

I want to fetch content from AEM between two particular dates by writing a query.

Can i get the help, what query should i exactly write to get this problem solved?

 

Thanks.

1 Accepted Solution

Avatar

Correct answer by
Employee Advisor

Let's suppose you want to retrieve list of pages based between current time and 14 days based on some criteria then you could refer below example -

 

map.put("path", pagepath.trim());
map.put("type", FMConstants.CQ_PAGE_NODETYPE);

map.put("1_group.daterange.property", "jcr:content/expires");
map.put("1_group.daterange.lowerBound", getCurrentDate());
map.put("2_group.relativedaterange.property", "jcr:content/expires");
map.put("2_group.relativedaterange.upperBound", "14d");
map.put("p.limit", "-1");

 

Please keep this also

View solution in original post

7 Replies

Avatar

Employee Advisor

Hi @Arindam15 

You can make use of daterange to write a query, a sample query looks like:

path=/content
daterange.property=cq:lastModified
daterange.lowerBound=2020-05-08T23:59:59
daterange.upperBound=2021-01-31T00:00:00

This will fetch all the content exisiting on path :/content from 08 may 2020 to 31 jan 2021

Please also refer references:
https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/aem-query-date-range-prope...

 

https://experienceleague.adobe.com/docs/experience-manager-65/developing/platform/query-builder/quer...

 

https://experienceleaguecommunities.adobe.com/t5/adobe-experience-manager/how-to-get-the-nodes-from-...

 

Avatar

Level 2

Let me show you what i did:

 

path=/content/retailBank
type=cq:page
relativedaterange.property=jcr:lastModified

relativedaterange.lowerBound=2021-01-18

relativedaterange.upperBound=2022-01-19

 

Here i am getting results, but the results stays same even if i change the dates, which means the dates are not working, even if i give lowerBound as 2019-01-01, the results are not changing. i have added time to the dates, then also same.

Please help

Avatar

Employee Advisor

 

 

DEBAL_DAS_0-1644832171147.png

Here you could see I am getting 361 pages.

Now I have changed the upperbound date and getting different count(results) -

DEBAL_DAS_1-1644832305672.png

 

Could you please review.

Avatar

Correct answer by
Employee Advisor

Let's suppose you want to retrieve list of pages based between current time and 14 days based on some criteria then you could refer below example -

 

map.put("path", pagepath.trim());
map.put("type", FMConstants.CQ_PAGE_NODETYPE);

map.put("1_group.daterange.property", "jcr:content/expires");
map.put("1_group.daterange.lowerBound", getCurrentDate());
map.put("2_group.relativedaterange.property", "jcr:content/expires");
map.put("2_group.relativedaterange.upperBound", "14d");
map.put("p.limit", "-1");

 

Please keep this also

Avatar

Community Advisor

Here is an example to find content between two dates

SELECT * FROM [nt:unstructured] AS s WHERE
path=/content/campaigns/
type=nt:unstructured
property=sling:resourceType
property.value=cq/personalization/components/campaignpage
1_daterange.property=jcr:created
1_daterange.upperBound=10111573478358
1_daterange.upperOperation=<=
2_daterange.property=jcr:created
2_daterange.lowerBound=1793478378


Result would be 

BrijeshYadav_0-1644581802900.png

 

Avatar

Level 2

Can you please explen me this line:

1_daterange.upperBound=10111573478358

what is this number specify?? How would i change this number based on my conditions?

Similary for the lowerBound dates i need help to understand the number pattern.

Thanks

Avatar

Community Advisor

Hi @Arindam15 

     Could you check https://hashimkhan.in/aem-adobecq5-code-templates/query-builder/ . You have pretty much everything you are looking for in here. Even though the blog is pretty old, all the content is valid unless there is any deprecated API , which is not there for query builder AFAIK

  • 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 “<=”

Some more useful links :- http://www.adobe.com.by/pdf/meetup-3/QueryBuilder_and_Custom_Predicates.pdf
Check page 11 for your requirement)

 

Thanks

Veena