Querying Content Repository based on Date not gving right results | Community
Skip to main content
A
amanagarwal
Level 3
February 23, 2018
Question

Querying Content Repository based on Date not gving right results

  • February 23, 2018
  • 6 replies
  • 5178 views

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

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.

    6 replies

    A
    amanagarwalAuthor
    Level 3
    February 26, 2018

    Any suggestions would be really helpful. Thanks...

    smacdonald2008
    smacdonald2008
    Level 10
    February 26, 2018

    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
      Community Manager
      kautuk_sahniCommunity Manager
      Community Manager
      February 27, 2018

      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-site · G…

      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!!

      Kautuk Sahni
        A
        amanagarwalAuthor
        Level 3
        March 1, 2018

        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?

        A
        amanagarwalAuthor
        Level 3
        March 1, 2018

        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
        smacdonald2008
        Level 10
        March 1, 2018

        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/#.WpdtJ6inGF4

        Terms & ConditionsAccessibility statement

        Loading footer...