orderby=@jcr:content/cq:lastModified is not returning result in sorted order.

[Umesh_Thakur]

Dear Members,

I am using below query to get the modified pages based on its last modification date and time.

path = /content/my-project/www/language-masters/en_ca
type=cq:Page
orderby=@jcr:content/cq:lastModified
orderby.sort=desc
p.limit=-1

It means last modified page should be displayed on the top of the result, returned by the query. but it is not returning data in the proper order.

In some of the environment it is working as expected but in one of my instance, we are facing the issue. 

Any thoughts on it?

Thanks

Umesh Thakur 

[Bhuwan_B]

@Umesh_Thakur Your query looks fine to me and it works fine when I tested it. Is their any specific error you are seeing in the instance you are testing ?

https://hashimkhan.in/2017/10/09/digest-the-query-builder-api-aem-search-tips/

[Umesh_Thakur]

No Bhuwan, not any specific error I can see in logs.

Issue is only with Ordering.

[Vijayalakshmi_S]

Hi @Umesh_Thakur,

Could you please execute the query in "Explain Query" in all the instances and confirm if it all uses cqPageLucene (Per the query shared, it should use OOTB cqPageLucene)

If it is the same in all instances, cross check if this node /oak:index/cqPageLucene/indexRules/cq:Page/properties/cqLastModified has ordered property [Boolean] set to true.

(It should be there OOTB, just a cross check if project is using custom Index and/or any specific property definition)

Is it possible for you to share specific case (with date values) where you see the discrepancy in the order.  

[Umesh_Thakur]

Thanks for the response @Vijayalakshmi_S,

Yes it is using cqPageLucene Index only in all the instances and I can see ordered property is true as well.

Please see the below image:

Working fine:

Not working:

in the last image, you can see pages modified this year, are coming third or forth position. that is the issue.

Date is in mm/dd/yyyy format.

we are displaying queried data in a html page. same result we are getting in query builder.

[Vijayalakshmi_S]

Hi @Umesh_Thakur,

Thanks for sharing screenshots. I tried reproducing this in my local(used the date values as it is in not working scenario) and it works fine.

Could you please let know

• If the order mismatch is occurring even when executed in Query Debugger - http://localhost:4502/libs/cq/search/content/querydebug.html
• If this implementation is already available + is working before and not working now in some instances?
  • If yes, was there any activity in the problematic instance related to custom index definition or anything related. 

[mustafaa6441711]

I have been through a similar issue. The value at the date field (in your case the value of cq:lastModified) had a wrong format, e.g:

cq:lastModified: 2023 2023

Of course 2023 2023 is an invalid date format.. it should be something like 2023-02-24. The consequence was that the data type of the cq:lastModified got changed by the system from DATE to STRING to match the value. All assets that had the wrong date format came first...