Managing Self Joins with Multiple Context in Neo4J

Example of Cypher Query using Relationship design pattern.

Query:

MATCH (manager:Employee)<-[rel:PRIMARY_REPORTS_TO|DOTTED_REPORTS_TO]-(e:Employee)
WHERE manager.name = "Alice"
RETURN e.name AS employee, 
       type(rel) AS relationship_type, 
       rel.role AS role, 
       rel.start_date, 
       rel.end_date;

Sample Graph Data:

Assume we have the following graph:

1. Bob reports to Alice as the primary manager.
2. Charlie reports to Alice as a dotted-line report for a project.
3. Diana reports to Alice as a dotted-line report for mentorship.

Query Results:

type(rel) AS relationship_type

1. Purpose of type(rel):
   • type(rel) retrieves the name of the relationship type for each match.
   • In this query, it can return either:
     • PRIMARY_REPORTS_TO
     • DOTTED_REPORTS_TO
   • This is useful when multiple types of relationships are being matched in the same query.
2. How It Works in the Query:
   • The query matches any rel of type PRIMARY_REPORTS_TO or DOTTED_REPORTS_TO between Alice (the manager) and her employees.
   • The type(rel) function determines whether the relationship is a primary or dotted-line report.
3. Practical Use:
   • It allows you to differentiate between primary and dotted-line reports in the result set.
   • For instance, in the results above:
     • Bob’s relationship is labeled as PRIMARY_REPORTS_TO.
     • Charlie and Diana’s relationships are labeled as DOTTED_REPORTS_TO.
4. Handling NULL Values:
   • In relationships like PRIMARY_REPORTS_TO, there may not be a role property (e.g., Bob has no role in his primary reporting line). This is reflected as NULL in the role column of the results.

Why This Query is Powerful

1. Insight Across Multiple Reporting Lines:
   • Combines both primary and dotted-line reporting relationships into a single result set.
   • Useful for identifying all employees managed by a specific manager, regardless of the type of reporting.
2. Dynamic Role Attribution:
   • The rel.role property allows capturing contextual information about the dotted-line relationships (e.g., “Project Lead” or “Mentor”).
3. Time-Bounded Relationships:
   • The start_date and end_date fields allow filtering based on active reporting periods. For example, you could modify the query to find employees who are currently reporting to Alice: MATCH (manager:Employee)<-[rel:PRIMARY_REPORTS_TO|DOTTED_REPORTS_TO]-(e:Employee) WHERE manager.name = "Alice" AND (rel.end_date IS NULL OR date(rel.end_date) >= date()) RETURN e.name AS employee, type(rel) AS relationship_type, rel.role, rel.start_date, rel.end_date;