Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

JCR query on node property instead of JCR creation/modifiy/publish date?

carolynr5682886
Level 2
Level 2

Hi Guys-

I am trying to write a query where I can get content from a location in AEM dated from today's day back 24 months, but the date is not based on the publish/modify/creation date. They are asking for the dates to be based on the content called the" publish release date". 

I have the following working off the creation date:

String sqlstmt = "SELECT * FROM [nt:unstructured] AS comp WHERE ISDESCENDANTNODE(comp, '"+pathLocation+"') AND [sling:resourceType] = 'nvenergy/components/content/pressrelease' AND comp.[jcr:created] >= CAST('"+julianEndDateString+"T00:00:00.000Z' AS DATE) AND comp.[jcr:created] < CAST('"+julianCurrentDateString+"T00:00:00.000Z' AS DATE) ORDER BY 1 ASC";

 

but I really need it to be off the content's "publish release date" found here: 

......

if (currentNode.hasProperty("pressreleasepublishdate")) {
                        Calendar publishedDate = currentNode.getProperty("pressreleasepublishdate").getDate();

....

Can I do a join or used something to use the node's property for the release date instead of the [jcr:created] as my range? Or do I have to execute the query hold it in a temp list and resort based on the node's property for the release date? 

1 Reply
smacdonald2008
Level 10
Level 10

I would test for that prop and then if its there - query for the range you want. 

carolynr5682886
Level 2
Level 2

So like this? 

String sqlstmt = "SELECT * from [nt:base] AS comp WHERE ISDESCENDANTNODE('"+pathLocation+"') AND (comp.[pressreleasepublishdate] <> '' AND comp.[pressreleasepublishdate] > CAST('"+julianCurrentDateString+"T00:00:00.000Z' AS DATE)) ORDER BY comp.[pressreleasepublishdate] ASC";

carolynr5682886
Level 2
Level 2

I don't think that I can get the values back for this property when running the query with the above request for the property value. Through the logger I am not seeing the value being replaced by the property name: 

16.05.2017 15:23:00.144 *INFO* [0:0:0:0:0:0:0:1 [1494973379652] GET /content/NVEnergy/en/about-nvenergy/news/news-releases.html HTTP/1.1] com.nvenergy.nvenergy.core.models.PressReleaseList SQL Statement: SELECT * from [nt:base] AS comp WHERE ISDESCENDANTNODE('/content/NVEnergy/en/about-nvenergy/news/') AND (comp.[pressreleasepublishdate] <> '' AND comp.[pressreleasepublishdate] > CAST('2017-05-16T00:00:00.000Z' AS DATE)) ORDER BY comp.[pressreleasepublishdate] ASC

 

When I run though the logic of looking at the child nodes property for ""pressreleasepublishdate"  from the result set I can get this:  

16.05.2017 17:41:23.672 *INFO* [0:0:0:0:0:0:0:1 [1494981683410] GET /content/NVEnergy/en/about-nvenergy/news/news-releases.html HTTP/1.1] com.nvenergy.nvenergy.core.models.PressReleaseList press release publishdate is : Nov 1, 2016