Query to retrieve 'top level' workspaces
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:
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.
You need to create workspace subtypes to do so (just a clone is OK).