Cmis query confinment

[leonardo_celati]

Hi all, I have a simple WCMQS collection query for retrieving asset marked with custom aspect I did.

select d.*, w.* from cmis:document as d join wzm:inmenu as w on d.cmis:objectId = w.cmis:objectId where w.wzm:menu = true order by w.wzm:rank
‍‍‍

It works fine, but it's retrieving all assets from the repository. Instead I would like to limit the collection query within its ws:root folder.

The assets with custom aspects are located in more than a folder, and the collection is at root folder.

Root (authoring)
+—>collection
+—>info
          +—>home.html (custom aspect)
+—>portfolio
          +—>whatever.html
‍‍‍‍‍‍‍‍

How can I accomplish that ?

[kaynezhang]

Use IN_TREE() function or CONTAINS() function  to narrow down the search at particular location
for example

select d.*, w.* from cmis:document as d join wzm:inmenu as w on d.cmis:objectId = w.cmis:objectId where w.wzm:menu = true order by w.wzm:rank and CONTAINS(d,'PATH:"//Sites"')
select d.*, w.* from cmis:document as d join wzm:inmenu as w on d.cmis:objectId = w.cmis:objectId where w.wzm:menu = true order by w.wzm:rank and IN_TREE(d, 'workspace://SpacesStore/5aece435-b231-468f-a306-1043fe26894a')
‍‍‍‍

[leonardo_celati]

Thanks, you gave me some useful tips. However, the point is that the path is hardcoded, this results in having two queries, one for the authoring folder and one for the publishing folder.

It would be perfect if this could be parametric, like

in_tree(d, ${startFromWebSiteRootWhereYouBelongThenLookInAllChildren})
‍‍‍

I hope you get the point 🙂

[leonardo_celati]

by the way is it possible to use where condition like the one below ?

.. AND (IN_TREE(d, 'path1') OR IN_TREE(d, 'path2')) ORDER BY ….
‍‍‍

I have tried but I am getting no result.

[kaynezhang]

You can use 

 AND (IN_TREE(d, 'path1') OR IN_TREE(d, 'path2')) ‍

The reason why you didn't get any result is that IN_TREE's parameter is node id .
it should be like this

workspace://SpacesStore/ce55a850-b1c7-43c7-a600-b5c58a985429‍