Multiple Outer Joins / OPTIONAL MATCH without a hierarchy in neo4j

Question

I am trying to match multiple outer joins on the same level in neo4j.

My database consists of users and a count of common up ur downratings on articles. The ratings counts are on seprate edges for up and downratings between the users.

----------                                -----------
| User n | -[:rating_positive {weight}]-> | User n2 |
----------                                -----------
     |                                         ^
      \-----[:rating_negative {weight}]-------/

Now i want to produce edges that sum up these ratings.

I would love to use multiple optional merges, that do so sch as e.g.:

MATCH (n:`User`)
OPTIONAL MATCH (n:`User`)-[rating_positive:rating_positive]-(n2:`User`)
OPTIONAL MATCH (n:`User`)-[rating_negative:rating_negative]-(n2:`User`)
RETURN n.uid, n2.uid, rating_positive.weight, rating_negative.weight

But: In this example I get all users without any positive ratings and those with positive and negatice ratings but none with only negative ratings. So there seems to be a sequence in OPTIONAL MATCH.

If I swap the order of the "OPTIONAL MATCHes" I get those with only negative ratings but not those with onl positive ratings.

So "OPTIONAL MATCH" is somehow a sequence where only when the first sequence is met I get something from the second and so on?
Is there a workaround?

Neo4j Version is 2.1.3.

P.S.:

Even more confusing matching against NULL does not seem to work. So this query:

MATCH (n:`User`)
OPTIONAL MATCH (n:`User`)-[rating_positive:rating_positive]-(n2:`User`)
OPTIONAL MATCH (n:`User`)-[rating_negative:rating_negative]-(n2:`User`)
WHERE rating_positive IS NULL AND rating_negative IS NOT NULL
RETURN n.uid, n2.uid, rating_positive.weight, rating_negative.weight

will give me lots of edges with NULL rating_negative and NON NULL rating_positive. I don't know what is happening with null matching in WHERE?

Anyway I found a way to recode the nulls to 0 values using "coalesce":

MATCH (n:`User`)
OPTIONAL MATCH (n:`User`)-[rating_positive:rating_positive]-(n2:`User`)
OPTIONAL MATCH (n:`User`)-[rating_negative:rating_negative]-(n2:`User`)
WITH n, n2, coalesce(rating_positive.weight, 0) AS rating_positive, coalesce(rating_negative.weight, 0) as rating_negative
WHERE rating_positive = 0 AND rating_negative > 0
RETURN n.uid, n2.uid, rating_positive, rating_negative

With this query it works as expected.

Luanne Luanne · Accepted Answer · 2014-08-05T09:42:25

I believe more than the sequencing of the optional match, it's the fact that you've bound n2. So the next optional match is restricted to only match the nodes identified to be candidates for n2 in the previous match. And so it appears that the order of the optional match influences it. If you take a look at a small sample graph I set up here http://console.neo4j.org/r/lrp55o , the following query

MATCH (n:User)
OPTIONAL
MATCH (n)-[:rating_negative]->(n2)
OPTIONAL
MATCH (n)-[:rating_positive]->(n2)
RETURN n,n2

returns B-[:rating_negative]->C and C-[:rating_negative]->D but it leaves out A-[:rating_positive]->B.

The first optional match for rating_negative bound C and D as nodes for "n2". The second optional match found no n which has a rating_positive to C or D and hence the results.

I'm a bit unclear about what you are trying to do with the query and null checks but a union would be one way to give you all the positive and negative relations (which you can add your filters to):

MATCH (n:User)
OPTIONAL
MATCH (n)-[rating:rating_negative]->(n2)
RETURN n,n2,rating.weight
UNION ALL 
MATCH (n:User)
OPTIONAL
MATCH (n)-[rating:rating_positive]->(n2)
RETURN n, n2, rating.weight

If this is not what you're looking for, a small subgraph at http://console.neo4j.org?init=0 would be great to help you further.

EDIT: Since comments indicated that the sum of ratings was required between a pair of users, the following query does the job:

MATCH (u:User)-[rating:rating_positive|:rating_negative]->(u2)
RETURN u,u2,sum(rating.weight)

Multiple Outer Joins / OPTIONAL MATCH without a hierarchy in neo4j

2 Answers