I've got a case where I have two entities in a hierarchical relationship, Sortie
has multiple child PlaneSortie
s. Both Sortie
or PlaneSortie
can have zero or more incoming HAS_ROLE
relationships from a PersonCluster
. I need to match on either Sortie
or PlaneSortie
and return some data that includes some property from Sortie
(if anything at all was matched) and optionally something from PlaneSortie
(if that was what was indeed matched, otherwise that property should be null).
Test data:
CREATE (pc:PersonCluster {id: "c42"}),
(pc)-[:HAS_ROLE {type: 'pilot'}]->(s1:Sortie {name: "S17"}),
(s2:Sortie {name: "S18"})-[:HAS_PLANE_SORTIE]->(ps:PlaneSortie {name: "PS18.1"}),
(pc)-[:HAS_ROLE {type: 'passenger'}]->(ps),
(s1)-[:HAS_LANDING_ZONE]->(l1:Location {name: "Nicaragua"}),
(s2)-[:HAS_LANDING_ZONE]->(l2:Location {name: "Grenada"});
Note: Sortie
always has either zero or one (but not more) :HAS_LANDING_ZONE
relationships to Location
.
This is my query that I believe works (unless I've accidentally rigged the test data). However this looks very convoluted to me, and empirically it's a bit slow. Is there a way I can rewrite it to be simpler? Speed is not my main concern here, but if it could be faster as well as simpler that would obviously be good. I feel like this is somehow related to UNION
queries in some way, but can't quite find a way to put an alternative together.
MATCH (pc:PersonCluster)
OPTIONAL MATCH (s1:Sortie)<-[r1:HAS_ROLE]-(pc)
OPTIONAL MATCH (s2:Sortie)-[:HAS_PLANE_SORTIE]->(ps:PlaneSortie)<-[r2:HAS_ROLE]-(pc)
WITH COLLECT(r1) + COLLECT(r2) AS relationships, pc AS pc
UNWIND relationships AS r
WITH COLLECT(DISTINCT r) AS r, pc AS pc
UNWIND r AS r2
OPTIONAL MATCH ()-[r2]->(s1:Sortie)
OPTIONAL MATCH ()-[r2]->(ps:PlaneSortie)<-[:HAS_PLANE_SORTIE]-(s2:Sortie)
WITH COALESCE(s1, s2) AS s, pc AS pc, ps AS ps, r2 AS r2
OPTIONAL MATCH (s)-[:HAS_LANDING_ZONE]->(l:Location)
RETURN {
sortieName: s.name,
planeSortieName: ps.name,
roleType: r2.type,
personClusterId: pc.id,
lzName: l.name
}
The query should return two rows that look like this:
{
"lzName": "Nicaragua",
"sortieName": "S17",
"personClusterId": "c42",
"planeSortieName": null,
"roleType": "pilot"
}
{
"lzName": "Grenada",
"sortieName": "S18",
"personClusterId": "c42",
"planeSortieName": "PS18.1",
"roleType": "passenger"
}
Basically it should return a row for every unique combination of (PersonCluster
,Sortie|PlaneSortie
,roleType
). lzName
and planeSortieName
are nullable if the respective relationships don't exist.