Department Hiërarchy

[peter_lous]

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?

[MuddyBootsCode]

I'm not sure you need that in your query at all. I may be wrong but your:

AND d.active

should exclude the departments where active = false anyway. No need to add a second query for it. I may have misunderstood what you're after but that's my first thought.

[peter_lous]

You are indeed right. However, I forgot to mention that the department hierarchy is a mirror of data in a relational database. In this database the active flag is not propagated to the child departments. A department may have its active flag set to false, but its children might have the active flag set to true. This has a reason: Suppose you have the following setup:

Department A: active = true
Department AA: active = false
Department AB active = true

Where Department AA and AB are child departments of Department A. If the active flag of Department A is set to false we can propagate this to Department AA and Department AB:

Department A: active = false
Department AA: active = false
Department AB active = false

However, when I make Department A active again and propagate this to the children you get the following result which is not equal to the starting position:

Department A: active = true
Department AA: active = true
Department AB active = true