topic Re: Simple CMIS query not working in Alfresco Archive

Simple CMIS query not working

leonardo_celati — Sat, 21 Jun 2014 14:36:41 GMT

I have a simple CMIS query which I am executing from within a collection folder. The purpose is to retrieve two specific documents placed in two different folders. SELECT d.* FROM cmis:document d JOIN ws:webasset w ON w.cmisbjectId = d.cmisbjectId WHERE (d.cmis:name = 'name1.html' AND in_fold

Re: Simple CMIS query not working

kaynezhang — Wed, 25 Jun 2014 03:01:42 GMT

Function IN_FOLDER's parameter is folder id instead of folder path.and you'd better add selector alias in IN_FOLDER function
Following is an example :


SELECT D.*, O.* FROM cmis:document AS D JOIN cm:ownable AS O ON D.cmis:objectId = O.cmis:objectId
WHERE 
   (D.cmis:name = 'name1.html' AND IN_FOLDER(D,'workspace://SpacesStore/e1e67158-b8ce-4d6b-aa46-0cc05cee9366'))
OR
   (D.cmis:name = 'name2.html' AND IN_FOLDER(D,'workspace://SpacesStore/e1e67158-b8ce-4d6b-aa46-0cc05cee9366'))
‍‍‍‍‍‍‍

Re: Simple CMIS query not working

leonardo_celati — Wed, 25 Jun 2014 09:27:26 GMT

Sorry, the missing qualifier was a typo error, the original query has it correctly specified. However, at this point it is not clear to me what ${section:/path} does.

If you look at the default dynamic collection created in the alfresco share wcmqs, the ${section:} is used everywhere. This is one of them.


SELECT d.* 
FROM cmis:document AS d JOIN ws:webasset AS wa ON d.cmis:objectId = wa.cmis:objectId 
WHERE d.cmis:objectTypeId='D:ws:article' AND in_tree(d, '${section:.}') 
ORDER BY wa.ws:publishedTime DESC
‍‍‍‍‍‍

and it works fine, and I am using it correctly everywhere in all my dinamic collections. The point is that it seems to stop working when I add another ${section:}.

Re: Simple CMIS query not working

kaynezhang — Wed, 25 Jun 2014 15:05:08 GMT

In CMIS query language,Function IN_FOLDER's parameter should be folder id as I posted .But when define dynamic Asset collections in wcm ,we can contain a special placeholder , the placeholder is automatically replaced by a section identifier(folder id) prior to executing.

So if you want to execute this type of query directly in node browser ,you should write a query as I posted ,Replace the placeholder(${section:.}) in your query to it's node id.
Node: you can get the section folder it using node browser (just navigate to the section folder and check it's node reference).

As your below query did not return anything,you should check whether subsection /root/folder1, /root/folder1 exist or not in your website.


SELECT d.* FROM cmis:document d JOIN ws:webasset w ON w.cmis:objectId = d.cmis:objectId 
WHERE 
   (d.cmis:name = 'name1' OR d.cmis:name = 'name2.html') 
AND 
   (in_folder('${section:/root/folder1}') OR in_folder('${section:/root/folder2}'))
‍‍‍‍‍‍‍

Re: Simple CMIS query not working

leonardo_celati — Wed, 25 Jun 2014 15:26:00 GMT

Thanks, in node browser works fine, with node id of course.

About the dynamic collection were the problem pertain to, I already made sure both folders and documents exists, as soon as I add another in_folder it stops retrieving documents.

Would you mind (in your spare time) to try the same kind of query for me, in your web quick start of course. Just to be sure there's no bug around, because it seems quite a strange behavior.

I am using 4.2.f community.

Re: Simple CMIS query not working

kaynezhang — Wed, 25 Jun 2014 15:45:53 GMT

I have not tested it yet.
If I have spare time I will test it and trace why.

For now ,just a suggestion,you can try with IN_TREE function as instead as following
<code>
SELECT d.* FROM cmis:document d JOIN ws:webasset w ON w.cmisbjectId = d.cmisbjectId

WHERE
(d.cmis:name = 'name1' OR d.cmis:name = 'name2.html')
AND
in_tree('${section:/root/}')
<code>