Querying Content Repository based on Date not gving right results

[amanagarwal]

Hi All,

We had a requirement in our project, where we query certain node in JCR which has multiple pages and we pull the results based on one jcr property on the page i.e. pubishDate. Theses results are ordered on the front end in form of tiles based on latest publishDate.

This field is filled by the Authors through Dialog box in one component. We have given the xtype of this field as DateField.

We have written the following xpath Query to pull results in Descending order:

/jcr:root/content/test/tagged-content/resource// element(*,nt:base)[(@multifield='{"identify":["Identifier:test1","Identifier:test2"]}')] order by @publishDate descending

Now the issue here is that we had given the format for Date in this dialog property as m/d/Y (this is default as well). As our Authors created the pages in 2017 and entered publish date as eg: 05/28/2017, 08/21/2017, 10/01/2017, etc, this worked fine all n the year 2017 as it was not the relevant factor in deciding the order if we consider same year. But now in 2018, this is not working fine as it is picking up the month first before year based on the property and most of the 2018 pages data is showing the end, though we want it to be shown first.

As we have already lot of content published in production, it is would not be a feasible solution to re-author all publish dates after changing the format to Y/m/d.

So we are looking for some better solution. Can someone suggest any query modification that can resolve this issue.

Another way i could think of is writing a service and modify all the publishDate values from m/d/Y format to Y/m/d e.g.: 08/21/2017 to 2017/08/21

If anyone has any better solution kindly suggest. Thanks..

Thanks,

Aman

[amanagarwal]

Any suggestions would be really helpful. Thanks...

[smacdonald2008]

Why are you not using JCR SQL2 to query date values??

AN example of using JCR SQL2 to compare dates:

String sqlStatement2 = "select * from [rep:User] where isdescendantnode('/home/users/mypath') and [jcr:created] > cast('"+ year +"-"+monthStr+"-"+dayStr+"T00:00:00.000+02:00' as date) and [jcr:created] < cast('"+ year2 +"-"+monthStr2+"-"+dayStr2+"T23:59:59.000+02:00' as date)";

  javax.jcr.query.Query query = queryManager.createQuery(sqlStatement2,"JCR-SQL2");

  //Execute the query and get the results ...

   javax.jcr.query.QueryResult result = query.execute();

   //Iterate over the nodes in the results ...

   javax.jcr.NodeIterator nodeIter = result.getNodes();

   while ( nodeIter.hasNext() ) {

                //For each node-- get the path of the node

                javax.jcr.Node node = nodeIter.nextNode();

                String myPath = node.getPath();

                //do something with path value....

}

[kautuk_sahni]

I would also recommend you using SQL2.

Reference article:- site/2014-10-07-9-jcr-sql-2-queries-every-aem-dev-should-know.html at master · klcodanr/danklco.com-...

If by date you mean expiery date:

SQL2 query :  SELECT p.* FROM [nt:base] AS p WHERE p.[prism:expirationDate] >= CAST('2016-01-01T00:00:00.000Z' AS DATE) AND p.[prism:expirationDate] <= CAST('2016-01-07T23:59:59.999Z' AS DATE)

(Note the date might need to be formatted using Java API to make this query dynamic)

Else you can also have a look at what Scott mentioned!!

[amanagarwal]

Thanks...

As I need to perform this activity of modifying node only once, on around 1500 nodes. What should be best approach for this; to create it as a component or implement Scheduler for particular time?

[amanagarwal]

We have already used "xpath" queries everywhere in our project, so using same to maintain consistency. Can the same be achieved using xpath queries? I could not find the CAST function in xpath query.

Does jcr-sql2 queries hold any advantage over xpath queries?

[smacdonald2008]

I personally find more ppl seem to work with JCR SQL2. Here is a nice blog that compares them: http://aempodcast.com/2015/aem-resources/aem-queries-xpath-jcr-sql2-query-builder-syntaxes/#.WpdtJ6i...