CMIS Query with IN_FOLDER

[blb]

HI !

I really don't understand why my query doesn't work.

SELECT cmis:versionSeriesId as id,cmis:name as name,cmis:contentStreamMimeType as type,cmis:creationDate as created_at,cmis:lastModificationDate as updated_at from cmis:document AS D where IN_FOLDER ( D, 'workspace://SpacesStore/4cd9b8a9-c06a-4ab5-a231-356c3b40c6d1') OR IN_FOLDER ( D, 'workspace://SpacesStore/b79cb920-c90b-4629-b8c0-0bdbc26fdf46') ORDER BY cmis:lastModificationDate DESC&succinct=true
‍‍‍

If my query containts just one IN_FOLDER everything seems to be ok.

I exec this query with curl.


Which is the "best" solution to retrieve all documents in a folder and his sub-folders ?

I use a recursive function to do this.

Thx !

[jpotts]

Your query works for me when run against Chemistry InMemory 0.11 and also Alfresco 5.0.d. I'm using the browser binding in both cases. I ran it using the Chemistry Workbench and also using the Alfresco Share node browser with "cmis-strict".

Here is the query I am using:

SELECT cmis:versionSeriesId as id,cmis:name as name,cmis:contentStreamMimeType as type,cmis:creationDate as created_at,cmis:lastModificationDate as updated_at from cmis:document AS D where IN_FOLDER ( D, 'workspace://SpacesStore/f8c2b20b-c981-4a16-8785-2ed70d19d2fe') OR IN_FOLDER ( D, 'workspace://SpacesStore/b0d93c53-d6ef-487e-ad25-614da73ab032') ORDER BY cmis:lastModificationDate DESC
‍‍‍

I noticed that your query has "&succinct=true" at the end, which should be part of the URL query params, not part of the CMIS query. Maybe that's the problem.

If you still can't get it working please post the entire curl command including the response.

Jeff

[blb]

I'm using php-curl on Alfresco Community v5.0.0 (r86473-b92) schema 8 006.

my script :

$condition = implode ( $this->WHERE );
        $url  = $this->host.'/alfresco/api/-default-/public/cmis/versions/1.1/browser?alf_ticket='.$this->token;
        $query = urlencode("SELECT cmis:versionSeriesId as id,cmis:name as name,cmis:contentStreamMimeType as type,cmis:creationDate as created_at,cmis:lastModificationDate as updated_at from cmis:document AS D where ".($condition)." ORDER BY cmis:lastModificationDate DESC");
        $data = "cmisaction=query&statement=".$query."&succinct=true";

$aResponse = $this->curlPost($url,"application/x-www-form-urlencoded",$data);

public function curlPost($url,$content_type,$data = null){
       $ch = curl_init();
      curl_setopt($ch, CURLOPT_URL, $url);
      curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
      curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
      curl_setopt($ch, CURLOPT_POSTFIELDS,$data);
      curl_setopt($ch, CURLOPT_HTTPHEADER, array(                                                                          
                   'Content-Type: '.$content_type,                                                                                
                   'Content-Length: ' . strlen($data)
                        )                                                                       
               );  
      $response = curl_exec ($ch);
      $httpcode = curl_getinfo($ch, CURLINFO_HTTP_CODE);
      curl_close($ch);
      return array('code'=>$httpcode,'json'=>$response);
    }

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

example of query (after urlencode) :

SELECT+cmis%3AversionSeriesId+as+id%2Ccmis%3Aname+as+name%2Ccmis%3AcontentStreamMimeType+as+type%2Ccmis%3AcreationDate+as+created_at%2Ccmis%3AlastModificationDate+as+updated_at+from+cmis%3Adocument+AS+D+where+IN_FOLDER+%28+D%2C+%27workspace%3A%2F%2FSpacesStore%2F61aef076-5813-4609-a3dc-0be1315170c1%27%29++OR+IN_FOLDER+%28+D%2C+%27workspace%3A%2F%2FSpacesStore%2F61aef076-5813-4609-a3dc-0be1315170c1%27%29+ORDER+BY+cmis%3AlastModificationDate+DESC
‍‍‍

<strong>Results With PHP-CURL :</strong>
Alfresco 5.0.0 -> error :

"exception": "invalidArgument",
  "message": "line 1:3054 mismatched input '<EOF>' expecting RPAREN  ([@458,3054:3054='<EOF>',<-1>,1:3054]) in where\n\t[query, whereClause, searchOrCondition, searchAndCondition, searchNotCondition, searchTest, predicate, folderPredicate, folderPredicateArgs, recoverFromMismatchedToken]",
‍‍‍‍

Alresco 5.0a -> no error, but my query only returns documents from first IN_FOLDER
Alresco 5.1 -> no error, but my query only returns documents from first IN_FOLDER

<strong>Results With Chrome Postman :</strong>
Alfresco 5.0.0 -> error :

"exception": "invalidArgument",
  "message": "line 1:3054 mismatched input '<EOF>' expecting RPAREN  ([@458,3054:3054='<EOF>',<-1>,1:3054]) in where\n\t[query, whereClause, searchOrCondition, searchAndCondition, searchNotCondition, searchTest, predicate, folderPredicate, folderPredicateArgs, recoverFromMismatchedToken]",
‍‍‍‍

Alresco 5.0a -> no error, but don't returns all documents
Alresco 5.1 -> no error, but don't returns all documents

hope to be "readable"

<strong>curl response :</strong>

curl -X POST -H "Content-Type: application/x-www-form-urlencoded" -H "Cache-Control: no-cache" -H "Postman-Token: 89972be7-5891-24e8-4b35-c616a3e74f5f" -d 'cmisaction=query&statement=SELECT cmis:versionSeriesId as id,cmis:name as name,cmis:contentStreamMimeType as type,cmis:creationDate as created_at,cmis:lastModificationDate as updated_at from cmis:document AS D where IN_FOLDER ( D, 'workspace://SpacesStore/8daadd1c-361a-4ae2-8eb8-925ca44ce7c8') OR IN_FOLDER ( D, 'workspace://SpacesStore/8daadd1c-361a-4ae2-8eb8-925ca44ce7c8') OR IN_FOLDER ( D, 'workspace://SpacesStore/8daadd1c-361a-4ae2-8eb8-925ca44ce7c8') OR IN_FOLDER ( D, 'workspace://SpacesStore/9dc0ce46-9a5d-450e-9c69-56a5b68faacf') ORDER BY cmis:lastModificationDate DESC&succinct=true' "http://localhost:8080/alfresco/api/-default-/public/cmis/versions/1.1/browser?alf_ticket=TICKET_6b2c..."
{"exception":"invalidArgument","message":"line 1:204 mismatched input 'workspace:' expecting QUOTED_STRING  ([@49,204:213='workspace:',<37>,1:204]) in where\n\t[query, whereClause, searchOrCondition, searchAndCondition, searchNotCondition, searchTest, predicate, folderPredicate, folderPredicateArgs, folderId, characterStringLiteral, recoverFromMismatchedToken]","stacktrace":"org.apache.chemistry.opencmis.commons.exceptions.CmisInvalidArgumentException: line 1:204 mismatched input 'workspace:' expecting QUOTED_STRING  ([@49,204:213='workspace:',<37>,1:204]) in where\n\t[query, whereClause, searchOrCondition, searchAndCondition, searchNotCondition, searchTest, predicate, folderPredicate, folderPredicateArgs, folderId, characterStringLiteral, recoverFromMismatchedToken]\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.characterStringLiteral(CMISParser.java:8302)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.folderId(CMISParser.java:7564)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.folderPredicateArgs(CMISParser.java:6895)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.folderPredicate(CMISParser.java:6733)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.predicate(CMISParser.java:5375)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.searchTest(CMISParser.java:5153)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.searchNotCondition(CMISParser.java:5032)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.searchAndCondition(CMISParser.java:4868)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.searchOrCondition(CMISParser.java:4749)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.whereClause(CMISParser.java:4670)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.query(CMISParser.java:356)\n\tat org.alfresco.opencmis.search.CMISQueryParser.parse(CMISQueryParser.java:136)\n\tat org.alfresco.repo.search.impl.solr.DbCmisQueryLanguage.executeQueryImpl(DbCmisQueryLanguage.java:129)\n\tat org.alfresco.repo.search.impl.solr.DbCmisQueryLanguage.executeQuery(DbCmisQueryLanguage.java:108)\n\tat org.alfresco.repo.search.impl.solr.DbOrIndexSwitchingQueryLanguage.executeQuery(DbOrIndexSwitchingQueryLanguage.java:186)\n\tat org.alfresco.repo.search.impl.solr.SolrOpenCMISQueryServiceImpl.query(SolrOpenCMISQueryServiceImpl.java:89)\n\tat sun.reflect.GeneratedMethodAccessor469.invoke(Unknown Source)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat org.alfresco.repo.management.subsystems.SubsystemProxyFactory$1.invoke(SubsystemProxyFactory.java:72)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)\n\tat com.sun.proxy.$Proxy146.query(Unknown Source)\n\tat org.alfresco.opencmis.CMISConnector.query(CMISConnector.java:2971)\n\tat org.alfresco.opencmis.AlfrescoCmisServiceImpl.query(AlfrescoCmisServiceImpl.java:2549)\n\tat sun.reflect.GeneratedMethodAccessor468.invoke(Unknown Source)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)\n\tat org.alfresco.repo.transaction.RetryingTransactionInterceptor$1.execute(RetryingTransactionInterceptor.java:79)\n\tat org.alfresco.repo.transaction.RetryingTransactionHelper.doInTransaction(RetryingTransactionHelper.java:457)\n\tat org.alfresco.repo.transaction.RetryingTransactionInterceptor.invoke(RetryingTransactionInterceptor.java:69)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)\n\tat org.alfresco.opencmis.AlfrescoCmisStreamInterceptor.invoke(AlfrescoCmisStreamInterceptor.java:72)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)\n\tat org.alfresco.opencmis.AlfrescoCmisServiceInterceptor.invoke(AlfrescoCmisServiceInterceptor.java:101)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)\n\tat org.alfresco.opencmis.AlfrescoCmisExceptionInterceptor.invoke(AlfrescoCmisExceptionInterceptor.java:83)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)\n\tat com.sun.proxy.$Proxy291.query(Unknown Source)\n\tat org.apache.chemistry.opencmis.server.support.wrapper.ConformanceCmisServiceWrapper.query(ConformanceCmisServiceWrapper.java:1157)\n\tat org.apache.chemistry.opencmis.server.impl.browser.DiscoveryService$Query.serve(DiscoveryService.java:76)\n\tat org.apache.chemistry.opencmis.server.shared.Dispatcher.dispatch(Dispatcher.java:91)\n\tat org.apache.chemistry.opencmis.server.impl.browser.CmisBrowserBindingServlet.dispatch(CmisBrowserBindingServlet.java:355)\n\tat org.apache.chemistry.opencmis.server.impl.browser.CmisBrowserBindingServlet.service(CmisBrowserBindingServlet.java:234)\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:731)\n\tat org.alfresco.opencmis.CMISServletDispatcher.execute(CMISServletDispatcher.java:188)\n\tat org.alfresco.opencmis.CMISWebScript.execute(CMISWebScript.java:51)\n\tat org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecute(RepositoryContainer.java:460)\n\tat org.alfresco.rest.api.PublicApiRepositoryContainer.transactionedExecute(PublicApiRepositoryContainer.java:45)\n\tat org.alfresco.repo.web.scripts.RepositoryContainer.transactionedExecuteAs(RepositoryContainer.java:649)\n\tat org.alfresco.repo.web.scripts.RepositoryContainer.executeScriptInternal(RepositoryContainer.java:421)\n\tat org.alfresco.repo.web.scripts.RepositoryContainer.executeScript(RepositoryContainer.java:301)\n\tat org.alfresco.rest.api.PublicApiRepositoryContainer.access$001(PublicApiRepositoryContainer.java:26)\n\tat org.alfresco.rest.api.PublicApiRepositoryContainer$1.doWork(PublicApiRepositoryContainer.java:81)\n\tat org.alfresco.repo.tenant.TenantUtil.runAsWork(TenantUtil.java:119)\n\tat org.alfresco.repo.tenant.TenantUtil.runAsTenant(TenantUtil.java:88)\n\tat org.alfresco.rest.api.PublicApiRepositoryContainer.executeScript(PublicApiRepositoryContainer.java:77)\n\tat org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:378)\n\tat org.springframework.extensions.webscripts.AbstractRuntime.executeScript(AbstractRuntime.java:209)\n\tat org.alfresco.repo.web.scripts.TenantWebScriptServlet.service(TenantWebScriptServlet.java:74)\n\tat org.alfresco.rest.api.PublicApiWebScriptServlet.service(PublicApiWebScriptServlet.java:53)\n\tat javax.servlet.http.HttpServlet.service(HttpServlet.java:731)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)\n\tat org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)\n\tat org.alfresco.repo.web.filter.beans.NullFilter.doFilter(NullFilter.java:68)\n\tat sun.reflect.GeneratedMethodAccessor462.invoke(Unknown Source)\n\tat sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)\n\tat java.lang.reflect.Method.invoke(Method.java:498)\n\tat org.alfresco.repo.management.subsystems.ChainingSubsystemProxyFactory$1.invoke(ChainingSubsystemProxyFactory.java:125)\n\tat org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)\n\tat org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)\n\tat com.sun.proxy.$Proxy282.doFilter(Unknown Source)\n\tat org.alfresco.repo.web.filter.beans.BeanProxyFilter.doFilter(BeanProxyFilter.java:82)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)\n\tat org.alfresco.web.app.servlet.GlobalLocalizationFilter.doFilter(GlobalLocalizationFilter.java:61)\n\tat org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)\n\tat org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)\n\tat org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:220)\n\tat org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:122)\n\tat org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:505)\n\tat org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)\n\tat org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)\n\tat org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)\n\tat org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:436)\n\tat org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1078)\n\tat org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:625)\n\tat org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)\n\tat java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)\n\tat java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)\n\tat org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)\n\tat java.lang.Thread.run(Thread.java:745)\nCaused by: MismatchedTokenException(37!=72)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.recoverFromMismatchedToken(CMISParser.java:169)\n\tat org.antlr.runtime.BaseRecognizer.match(BaseRecognizer.java:115)\n\tat org.alfresco.repo.search.impl.parsers.CMISParser.characterStringLiteral(CMISParser.java:8259)\n\t… 98 more\n"}
‍‍‍‍

[jpotts]

My advice is to convince yourself that the query is okay by running it in Chemistry Workbench or in the node browser. Once you've got it working, then you can look at why the PHP code might be sending it to the server incorrectly.

Jeff

[blb]

You'r right.

my query was not correctly formatted. that's all.