We modeled a department hiërarchy where each department can have zero or more sub departments. The model is as follows:
(:Department)-[:SUBDEPARTMENT]->(:Department)
A user can be connected to a department with a specific role. This means that the user has that role for the connected department and all its sub departments.
To query for which departments the user has a specific role looks as follows:
MATCH (u:User)-[:SOME_ROLE]->(d:Department)
WHERE u.userid = "<userid>"
WITH COLLECT(d) AS departments
OPTIONAL MATCH (u:User)-[:SOME_ROLE]->(:Department)-[:SUBDEPARTMENT*]->(d:Department)
WHERE u.userid = "<userid>"
WITH departments + COLLECT(d) AS all_departments
UNWIND all_departments AS department
RETURN department
ORDER BY department.name
Departments also have a boolean property "active". If true it is an active department, if false it is an inactive department. When the active flag is false it must also apply to the sub departments: when querying for departments for which a user has a specific role, departments with the active flag set to false should be excluded, including their sub departments. Currently I implemented this as follows:
MATCH (u:User)-[:SOME_ROLE]->(d:Department)
WHERE u.userid = "<userid>"
AND d.active
WITH COLLECT(d) AS departments
OPTIONAL MATCH (u:User)-[:SOME_ROLE]->(:Department)-[:SUBDEPARTMENT*]->(d:Department)
WHERE u.userid = "<userid>"
AND d.active
AND NOT (d)<-[*]-(:Department{active:false})
WITH departments + COLLECT(d) AS all_departments
UNWIND all_departments AS department
RETURN department
ORDER BY department.name
So with the condition:
AND NOT (d)<-[*]-(:Department{active:false})
I validate if in the department hierarchy upwards none of the departments have the active flag set to false. However this fires an additional subquery and I wonder if there is a better way to achive this, or change the model to make the query more effeciënt?