CMIS query with a date

[agz]

Hi all,
I have my Alfresco Community 4.0.e with Solr and I try to get documents through CMIS.

I have my custom model defines a metadata (

ged:startDate‍

) with a d:date type (like cmis:creationDate).

When i try to execute a query through CMIS workbench (for testing query) I'm not able to compare date:

SELECT D.* 
FROM cmis:document as D 
JOIN ged:cotractModel AS C ON D.cmis:objectId = C.cmis:objectId  
C.ged:startDate<TIMESTAMP '2013-05-22T13:59.54+02.00'
‍‍‍‍‍‍

The workbench give me "Internal server error"

(The same query can be executed using

D.cmis:creationDate‍

instead

C.ged:startDate‍

)

In my Alfresco I have setted

d_dictionary.datatype.d_datetime.analyzer=org.alfresco.repo.search.impl.lucene.analysis.DateTimeAnalyser‍

but I have same errors.

Where is the problem?

I hope someone can helps me.
Thanks.

[agz]

Anyone can help me???

[jspuchau]

From what I see, I'm not sure if your datetime format is correct.

In similar queries I use a format like this one: cmis:lastModificationDate < TIMESTAMP '2014-05-22T00:00:00.000+00:00' that is different than yours.

Anyway, you should try to get debug log traces or a description on the Internal Server Error received.

Regards,

[agz]

I tried with your sintax

SELECT C.*

FROM cmis:document as D  
JOIN ged:contractModel AS C ON D.cmis:objectId = C.cmis:objectId  

WHERE (IN_FOLDER(D, 'workspace://SpacesStore/f8e680b8-11b9-4efb-93fa-65a090173813') 
C.ged.startDate < TIMESTAMP '2014-05-22T00:00:00.000+00:00'
‍‍‍‍‍‍‍‍‍

but I have this log:

27-mag-2013 11.41.36 org.apache.solr.common.SolrException log
GRAVE: org.apache.chemistry.opencmis.commons.exceptions.CmisInvalidArgumentException: line 1:422 no viable alternative at input '.'
    (decision=28 state 78) decision=<<569:1: predicate : ( comparisonPredicate | inPredicate | likePredicate | nullPredicate | quantifiedComparisonPredicate | quantifiedInPredicate | textSearchPredicate | folderPredicate );>> in where
   [query, whereClause, searchOrCondition, searchAndCondition, searchNotCondition, searchTest, predicate]
   at org.alfresco.repo.search.impl.parsers.CMISParser.predicate(CMISParser.java:3347)
   at org.alfresco.repo.search.impl.parsers.CMISParser.searchTest(CMISParser.java:3099)
   at org.alfresco.repo.search.impl.parsers.CMISParser.searchNotCondition(CMISParser.java:3015)
   at org.alfresco.repo.search.impl.parsers.CMISParser.searchAndCondition(CMISParser.java:2870)
   at org.alfresco.repo.search.impl.parsers.CMISParser.searchOrCondition(CMISParser.java:2724)
   at org.alfresco.repo.search.impl.parsers.CMISParser.whereClause(CMISParser.java:2645)
   at org.alfresco.repo.search.impl.parsers.CMISParser.query(CMISParser.java:343)
   at org.alfresco.opencmis.search.CMISQueryParser.parse(CMISQueryParser.java:135)
   at org.alfresco.solr.AlfrescoSolrDataModel.parseCMISQueryToAlfrescoAbstractQuery(AlfrescoSolrDataModel.java:1038)
   at org.alfresco.solr.query.CmisQParserPlugin$CmisQParser.parse(CmisQParserPlugin.java:88)
   at org.apache.solr.search.QParser.getQuery(QParser.java:131)
   at org.apache.solr.handler.component.QueryComponent.prepare(QueryComponent.java:89)
   at org.apache.solr.handler.component.SearchHandler.handleRequestBody(SearchHandler.java:174)
   at org.apache.solr.handler.RequestHandlerBase.handleRequest(RequestHandlerBase.java:131)
   at org.apache.solr.core.SolrCore.execute(SolrCore.java:1316)
   at org.apache.solr.servlet.SolrDispatchFilter.execute(SolrDispatchFilter.java:338)
   at org.apache.solr.servlet.SolrDispatchFilter.doFilter(SolrDispatchFilter.java:241)
   at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:235)
   at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
   at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
   at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
   at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:563)
   at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
   at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:102)
   at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
   at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:298)
   at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:857)
   at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.process(Http11Protocol.java:588)
   at org.apache.tomcat.util.net.JIoEndpoint$Worker.run(JIoEndpoint.java:489)
   at java.lang.Thread.run(Thread.java:662)
Caused by: NoViableAltException(37@[569:1: predicate : ( comparisonPredicate | inPredicate | likePredicate | nullPredicate | quantifiedComparisonPredicate | quantifiedInPredicate | textSearchPredicate | folderPredicate );])
   at org.antlr.runtime.DFA.noViableAlt(DFA.java:158)
   at org.antlr.runtime.DFA.predict(DFA.java:144)
   at org.alfresco.repo.search.impl.parsers.CMISParser.predicate(CMISParser.java:3220)
   … 29 more

‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[marcus_svensson]

Your query does not look correct, you are missing an AND and a paranthesis.

SELECT C.*
 
FROM cmis:document as D  
JOIN ged:contractModel AS C ON D.cmis:objectId = C.cmis:objectId  
 
WHERE (IN_FOLDER(D, 'workspace://SpacesStore/f8e680b8-11b9-4efb-93fa-65a090173813') AND
C.ged.startDate < TIMESTAMP '2014-05-22T00:00:00.000+00:00')
‍‍‍‍‍‍‍‍‍

[agz]

Now it works!
Thanks marcus.

Probably my problem was timestamp format and ( ) !

[raghav]

Does the above query work with equal comparison of time?