Mark Needham

Thoughts on Software Development

neo4j: Handling optional relationships

without comments

On my ThoughtWorks neo4j there are now two different types of relationships between people nodes – they can either be colleagues or one can be the sponsor of the other.

The graph looks like this:

Sponsors colleagues

I wanted to get a list of all the sponsor pairs but also have some indicator of whether the two people have worked together.

I started off by getting all of the sponsor pairs:

START n = node(*) 
MATCH n-[r:sponsor_of]->n2
RETURN n.name, n2.name

I managed to narrow that down to the people who sponsored someone that they’d worked with like so:

START n = node(*) 
MATCH n-[r:sponsor_of]->n2, n-[r2:colleagues]->c
WHERE c = n2
RETURN n.name, n2.name

But it wasn’t quite what I wanted since I’d now lost all the sponsor pairs who didn’t work together.

My next attempt was to remove the WHERE clause and try the following which isn’t even a valid cypher query:

START n = node(*) 
MATCH n-[r:sponsor_of]->n2, n-[r2:colleagues]->c
RETURN n.name, n2.name, n2 IN [c]

I was struggling so I decided to draw out the above diagram and then work backwards from the type of output which I expected if I had the correct query.

The output I wanted was like this:

PersonA | PersonB | Sponsor Relationship | Colleague Relationship
PersonA | PersonC | Sponsor Relationship | -

Once I had written it out on paper it became clear that what I needed to do was find all the sponsor pairs and then optionally look for a colleagues relationship between the pair:

START n = node(*)  
MATCH n-[r:sponsor_of]->n2-[r2?:colleagues]->n 
RETURN n.name, n2.name, r, r2

The ‘?’ before the ‘:’ in the colleagues relationship indicates that it’s optional and will still return the traversal even if that relationship doesn’t exist.

If we run that query in the console it does exactly what we want:

==> +--------------------------------------------------------------------------------------+
==> | n.name            | n2.name            | r                      | r2                 |
==> +--------------------------------------------------------------------------------------+
==> | "PersonA"         | "PersonB"          | :sponsor_of[261255] {} | :colleagues[217292]|
==> | "PersonA"         | "PersonC"          | :sponsor_of[261252] {} | <null>             |
==> +--------------------------------------------------------------------------------------+

Written by Mark Needham

June 24th, 2012 at 11:32 pm

Posted in neo4j

Tagged with ,