Cypher Query for multiple nodes and relationship selection

[vinayak_bali]

The schema is as shown in the image uploaded. Need to generate a query for multiple selections of nodes and relationships for returning data as well as a separate query for the count with filters applied. Tried the following query:

MATCH (Ve_0:Node4)-[Su_2:Supply]->(Ma_1:Node3) WHERE Ma_1.MaterialNumber = '16979'
OPTIONAL MATCH (Ma_1)-[St_5:Stored_In]->(Pl_4:Node2)
OPTIONAL MATCH (Pl_4)-[Sh_8:Shipped_To]->(Cu_7:Node1)
OPTIONAL MATCH (Ma_1)-[De_11:Deliver_To]->(Cu_7)
OPTIONAL MATCH (Ve_0)-[Su_14:Supply_To]->(Pl_4)
RETURN Ve_0,Ma_1,Su_2,Pl_4,St_5,Cu_7,Sh_8,De_11,Su_14
LIMIT 100

The above query doesn't return the data.

MATCH (Ve_0:Node4)-[Su_2:Supply]->(Ma_1:Node3)
OPTIONAL MATCH (Ma_1)-[St_5:Stored_In]->(Pl_4:Node2)
OPTIONAL MATCH (Pl_4)-[Sh_8:Shipped_To]->(Cu_7:Node1)
OPTIONAL MATCH (Ma_1)-[De_11:Deliver_To]->(Cu_7)
OPTIONAL MATCH (Ve_0)-[Su_14:Supply_To]->(Pl_4)
WHERE Ma_1.MaterialNumber = '16979'
RETURN Ve_0,Ma_1,Su_2,Pl_4,St_5,Cu_7,Sh_8,De_11,Su_14
LIMIT 100

The Above query returns data but the filter is not applied.

How to generate an optimized query in such scenarios along with filters on multiple nodes and relationships??

[giuseppe_villan]

@vinayak.bali
This is weird.
I created a simple dataset like this:

CREATE (Ve_0:Node4)-[Su_2:Supply]->(Ma_1:Node3) SET Ma_1.MaterialNumber = '16979'
    with Ve_0, Ma_1
CREATE (Ma_1)-[St_5:Stored_In]->(Pl_4:Node2)
with Ve_0, Ma_1, Pl_4
CREATE (Pl_4)-[Sh_8:Shipped_To]->(Cu_7:Node1)
with Ve_0, Ma_1, Pl_4, Cu_7
CREATE (Ma_1)-[De_11:Deliver_To]->(Cu_7)
with Ve_0, Ma_1, Pl_4, Cu_7
CREATE (Ve_0)-[Su_14:Supply_To]->(Pl_4)

and it works with both queries.

Anyway, the second query returns data because the WHERE condition is applied to the OPTIONAL MATCH, so it will be returned data even if the where clause doesn't match anything (but in this case, the Su_14 column will be null)

I'm not sure , but I would say the where is probably wrong.
Can you provide a simple dataset to try this one?

[vinayak_bali]

@giuseppe.villani Unfortunately, I don't have demo data for the use case, and generating it is a tedious process. If you can share a generic query based on the data model shared for all the selection of nodes and relationship with the filter on all, that will help.