I need to query the jcr repository to find nodes where a date property (e.g. jcr:created) is younger than a specific date.
Using SQL2, I do the check "jcr:created > date" like that (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's an additional property which declares a number of days which have to be added to the jcr:created date dynamically.
Let's say the property contains 5 (days) then the query should not check "jcr:created > date" but rather "(jcr:created + 5) > date". The next node containing the property value 10 should be checked by "(jcr:created + 10) > date".
Is there any intelligent / dynamic operand which could do that? As the property is node specific I cannot add it statically to the query but it has to read it of each node.
Jackrabbit doesn't currently support such dynamic constraints.
I believe the best solution for now is to run the query with a fixed date constraint and then explicitly filter the results by yourself.
An alternative solution would be to precompute the "jcr:created + extratime" value and store it in an additional property. Such computation could either be located in the code that creates/updates the nodes in the first place, or you could place it in an observation listener so it'll get triggered regardless of how the node is being modified.
I had a need to find documents created in last 12 hours
I had a hard time how to get a valid date in the CAST function, Pasting for others who may need it.
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)";
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With