How to do grouped joins

[rafikj]

I am an advanced SQL and SPARQL user and I am currently finding it difficult
to map some concepts and patterns to Cypher.

I did read the documentation and I find the examples are too simple.
I need to express the following traceability path in Cypher.

a-[r1(optional)]->b-[r2(required)]->c-[r3(optional)]->d-[r4(required)]->
e-[r5(required)]->f-[r6(optional)]->g

With SQL, I can do

SELECT a.NAME, b.NAME, FROM A a
LEFT OUTER JOIN (B b
	INNER JOIN (C c
		LEFT OUTER JOIN (D d
			INNER JOIN (E e
				INNER JOIN (F f
					LEFT OUTER JOIN G g ON g.r6_ID = f.ID
				) ON r5.e_ID = e.ID
			) ON e.r4_ID = d.ID
		) ON r3.c_ID = c.ID
	) ON c.r2_ID = b.ID
) ON b.r1_ID = a.ID

WITH SPARQL, I can do:

OPTIONAL {
	?a ?r1 ?b.
	?b ?r2 ?c.
	OPTIONAL {
		?c ?r3 ?d.
		?d ?r4 ?e.
		?e ?r5 ?f.
		OPTIONAL {
			?f ?r6 ?g.
		}
	}
}

I just cannot find a way to nest matches in Cypher and mimic the behavior 
of grouped joins in both SQL and SPARQL.

[intouch_vivek]

Hi @rafikj,

The cypher query will depend on the model you have implemented in neo4j. For an example please visit https://neo4j.com/developer/guide-sql-to-cypher/ and see the join section. You will see that 4 tables are used in SQL join however few labels are used in the cypher query.

Regards
vivek

[andrew_bowman]

It will definitely help if you provided labels or relationships.

Let's say that we have a node labeled :Node with id 12345 for the start, with multiple relationships (we'll use :r1 through :r6 for their type) for the pattern.

We can use a variant of variable-length relationship patterns to use a *0..1 to represent an optional traversal.

So our MATCH might be something like:

MATCH (start:Node {id:12345})-[:r1*0..1]->(b)-[:r2]->(c)-[:r3*0..1]->(d)-[:r4]->(e)-[:r5]->(f)-[:r6*0..1]->(g)
...

[rafikj]

Thanks Andrew,
That should work. The pattern below seems to also work for me.
MATCH (a)
OPTIONAL MATCH (a)-[:r1]->(b)-[:r2]->(c)
OPTIONAL MATCH (c)-[:r3]->(d)-[:r4]->(e)-[:r5]->(f)
OPTIONAL MATCH (f)-[:r6]->(g)

[andrew_bowman]

Keep in mind that each successive OPTIONAL MATCH will only execute if the prior ones found a match.

My understanding of your need (looking at just a subset of your requirement, a-[r1(optional)]->b-[r2(required)]->c ), was that :r1 may or may not be present, but either way, continue traversing :r2 to node c.

So both of these paths should work:

(a)-[:r1]->(b)-[:r2]->(c)
(a)-[:r2]->(c)  

In the last case, r1 was optional, so when it's not present a and b are the same node, since a relationship :r1 was traversed 0 times

The query I provided will match both of those.

Your OPTIONAL MATCH approach won't. So these approaches are not really interchangeable except in narrow cases. Consider what results you are expecting, and select whichever approach will get you the correct one.

[rafikj · Accepted solution]

I think now I got it. The behavior I was actually looking for is indeed:
MATCH (a)
OPTIONAL MATCH (a)-[:r1]->(b)-[:r2]->(c)
OPTIONAL MATCH (c)-[:r3]->(d)-[:r4]->(e)-[:r5]->(f)
OPTIONAL MATCH (f)-[:r6]->(g)
as described by the SQL and SPARQL representations.

So in my case here are the paths that I expect to work:
(a)
(a)-[:r1]->(b)-[:r2]->(c)
(a)-[:r1]->(b)-[:r2]->(c)-[:r3]->(d)-[:r4]->(e)-[r5]->(f)
(a)-[:r1]->(b)-[:r2]->(c)-[:r3]->(d)-[:r4]->(e)-[r5]->(f)-[:r6]->(g)

[andrew_bowman]

Glad to hear it, sounds like you have everything you need for your case. You now have several useful tools in your belt to handle a variety of use cases for optional path matching.