Fetching pages by modifed date in List component

[nj2]

Hello Community Members, 

could you please suggest the query I should use in the following list component to retrieve pages located on /content/abc/ path filtered by their last modified date and only showing the pages modified within last 2 days

https://experienceleague.adobe.com/docs/experience-manager-core-components/using/wcm-components/list...

[Harwinder-singh]

@nj2  You can do something like this : 

SELECT page.* FROM [cq:Page] AS page
INNER JOIN [cq:PageContent] AS jcrContentNode ON ISCHILDNODE(jcrContentNode, page)
WHERE ISDESCENDANTNODE(page, "/content/abc")
AND jcrContentNode.[cq:lastModified] <= CAST("2023-01-01T00:00:00.000+00:00" AS DATE)

The value that I am trying to cast as Date can be any date time stamp value that you wish to filter out the results with.

[aanchal-sikka]

@nj2 

There are multiple part you would need to fix:

1. Resolving the current date

We require a method to determine the present date. This might involve extending the functionality of the list component to generate a query that takes the current date into account, execute the query, and provide the resulting data.

2. Executing query:

@Harwinder-singh has shared the query, which you can readily use after resolving the current date

[kautuk_sahni]

@nj2  Did you find the suggestions from users helpful? Please let us know if more information is required. Otherwise, please mark the answer as correct for posterity. If you have found out solution yourself, please share it with the community.

[nj2]

Hi Kautuk, 

I appreciate the suggestion, and many thanks for that,  but the issue remains unresolved. I was hoping for an out of the box solution and would prefer not to make any code changes due to certain deployment limitations. 

[Harwinder-singh · Accepted solution]

@nj2  Unfortunately there is no out of the box option to fulfill this 'very specific' need.

You will have to go for a custom solution option as described in this thread.