SQL2 JCR query with dynamic date matching

Question

I need to request a jcr repository to search for nodes where the date property (e.g. jcr: created) is less than a specific date.

Using SQL2, I check "jcr: created> date" (which works fine):

SELECT * FROM [nt:base] AS s WHERE s.[jcr:created] > CAST('2012-01-05T00:00:00.000Z' AS DATE)

Now the tricky part:

There is an additional property that declares a few days to be added to jcr: the created date dynamically.

Let's say the property contains 5 (days), then the request should not check "jcr: created> date", but rather "(jcr: created + 5)> date". The next node containing the value of property 10 should be checked with "(jcr: created + 10)> date".

Is there an intelligent / dynamic operand that can do this? Since the node property is specific, I cannot add it statically to the request, but it must read it for each node.

Answer 1

Jackrabbit does not currently support such dynamic restrictions.

I believe that the best solution at the moment is to run the query with a fixed date limit, and then explicitly filter the results yourself.

An alternative solution would be to recompute the value of "jcr: created + extratime" and store it in an additional property. Such a calculation can either be located in the code that creates / updates the nodes in the first place, or you can put it in the monitoring listener so that it works regardless of how the node changes.

Answer 2

I needed to find documents created in the last 12 hours

It was hard for me to find a valid date in the CAST function, an insert for others you might need.

 SimpleDateFormat dateFromat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSS'Z'"); cal.setTime(cal.getTime()); cal.add(Calendar.HOUR, -12); String queryString = "SELECT * FROM [nt:base] AS s WHERE " + "ISDESCENDANTNODE([/content/en/documents/]) " + "and s.[jcr:created] >= CAST('"+dateFromat.format(cal.getTime())+"' AS DATE)"; 

Answer 3

I found a recipe there: test.sql2.txt

List of tests. My query looks like this:

 SELECT * FROM [nt:base] where [jcr:created] > cast('[phone]T00:00:00.000Z' as date) 

Everything inside the casting line is required: +yyyy-MM-ddT00:00:00.000Z