Query to retrieve 'top level' workspaces
Hello community,
In the project I'm working on we have a workspace structure where different workspaces are nested, each workspace associated with user groups and read permissions, i.e:
Workspaceroot
Workspace 1
Workspace 1a
Workspace 1b
Workspace 2
Workspace 2a
Workspace 2b
Workspace 2b-1
Workspace 2b-2
Workspace 3
User group 1 has permissions on Workspace 1 and its children User group 2 has permissions on Workspace 2 and its children User group 2b has permissions on Workspace 2b and its children etc.
I need to build a query to retrieve only the top level workspaces associated with the user group, without children workspaces. That is, user group 2 should only see Workspace 2 without children, while user group 2b should only get Workspace 2b without parent or children and so on.
So far I have a query that retrieves workspaces from a path using the STARTSWITH keyword: SELECT * FROM Document WHERE ecm:mixinType != 'HiddenInNavigation' AND ecm:isProxy = 0 AND ecm:isVersion = 0 AND ecm:isTrashed = 0 AND ecm:primaryType='Workspace' and ecm:path STARTSWITH '/default-domain/workspaces' ORDER BY dc:created DESC
But this query retrieves all workspaces and their children. I also tried using ecm:pos = 0
but this retrieves null registers.
Is there a way to limit the search path to only the top level workspaces? Also, if I use the STARTSWITH keyword, how do I make it a dynamic parameter so that user group 2b searches for '/default-domain/workspaces/Workspace 2/Workspace 2b'
?
Sorry if I don't make much sense, but I'm new to NXQL queries.
If I understood the requirement correctly, you would need to query something like SELECT * FROM Workspace WHERE ecm:mixinType != 'HiddenInNavigation' AND ecm:isProxy = 0 AND ecm:isVersion = 0 AND ecm:isTrashed = 0 order by ecm:path
and get only the first result (This.get(0)
in an automation chain) … It would retrieve the one document with the shortest path a given user can read. A bit an overkill given you retrieve all workspaces to keep only 1 in the end. You can replace the Workspace type with your own derived type.
Hello,
You need to create workspace subtypes to do so (just a clone is OK).
Regards