Query to retrieve 'top level' workspaces

[a_c]

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.

[Gregory_Carlin]

Hello,

You need to create workspace subtypes to do so (just a clone is OK).

Regards

[a_c]

Thank you for your reply but can you elaborate? I already declared subtypes at the doctype extension point, which as far as I know are used to determine the types that can be created inside a parent document. But what do you mean with a clone and how would I use it in a query?

[Patrick_Abgrall]

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.

[a_c]

Thank you for your kind reply. I opted for working with the query I had and then just filtered the values using JS, basically the suggestion you gave me without resorting to automation chains