Cypher Query- Last Node & Repetition Issue

Hi everyone,

I have written a cypher query to calculate the sum of its next connected node, but for the lowest (last) node where there is no next connected node, how to make the below query work.

match p=(a:RM)-[r1:transport]->(b:RM)-[r2:transport]->(c:RM)-[r3:transport]->(d:WIPProduction)-[r4:transport]->(e:WIPStorage)-[r5:transport]->(f:WIPStorage)-[r6:transport]->(g:WIPProduction)-[r7:transport]->(h:WIPStorage)-[r8:transport]->(i:WIPStorage)-[r9:transport]->(j:WIPStorage)-[r10:transport]->(k:Production)-[r11:transport]->(l:Storage)-[r12:transport]->(m:Storage)
where a.Date=date('2021-10-01')
with p,a,r1,b,r2,c,r3,d,r4,e,r5,f,r6,g,r7,h,r8,i,r9,j,r10,k,r11,l,r12,m, case when m.end_point=1 then 0 else sum((r13.quota)*(r13.bom_qty)*(n.Inflow_Requirement)) end as downstream_inflow_requirement
set m.Outflow_Requirement_Updated=downstream_inflow_requirement
with p,a,r1,b,r2,c,r3,d,r4,e,r5,f,r6,g,r7,h,r8,i,r9,j,r10,k,r11,l, 
case when l.end_point=1 then 0 else sum((r12.quota)*(r12.bom_qty)*(m.Inflow_Requirement)) end as downstream_inflow_requirement
set l.Outflow_Requirement_Updated=downstream_inflow_requirement 
with p,a,r1,b,r2,c,r3,d,r4,e,r5,f,r6,g,r7,h,r8,i,r9,j,r10,k, case when k.end_point=1 then 0 else sum((r11.quota)*(r11.bom_qty)*(l.Inflow_Requirement)) end as downstream_inflow_requirement
set k.Outflow_Requirement_Updated=downstream_inflow_requirement 
return p ;

This formula has to repeat for all nodes from m to a ( I have written till k only), There are 2 issues I am facing here:

  1. In the first with statement I am getting an error that variable r13, n is not defined , so how should i structure the formula to make it work as the formula has to be standard for all the nodes?

  2. Is there any way that instead of repeating the same formula for different set of nodes till a, just if I put a parameter or something and that automatically cypher shifts and traverses the path for all variables ?

Please let me know if anyone has the solution for this.

Thanks,
Pragya

Let's see if we can simplify it.

By the properties you're setting, it doesn't look as if the Outflow_Requirement_Updated property is used when calculating any other node's Outflow_Requirement_Updated, so it doesn't look like there's any ordering requirements we need to enforce for correctness.

There are a variety of labels being used in this long pattern. Are those significant? Are there other patterns of :transport relationships going to nodes of other labels that you don't want to process? If the labels aren't that important, and you want to process nodes of any label along an outgoing :transport chain from your starting node, then we can make this query much easier.

Is Outflow_Requirement_Updated already present on these nodes before running the query, or is this being newly added with this query?

Do you need to get the return of the paths, or is the setting of properties enough?

Hi Andrew,

Outflow_Requirement_Updated is a propety which is newly added with this query, I need to start the calculations from the lowest node which is (m:Storage) and then move towards (a:RM) so this order has to be maintained.

The variety of labels used in this long pattern are significant as there are multiple paths present in my graph with variety of labels and different lengths, and there are more properties that I am actually calculating , I had just shared a snippet of one property which is Outflow_Requirement_Updated.

And I don't want to return the paths just setting the properties of the nodes is enough.

Thanks,
Pragya

Thanks for the clarification, though maybe I'm missing something about your claim that the order of calculation has to be maintained, since the calculations for upstream nodes don't seem to be dependent on downstream node calculations.

We are however dependent upon the matching of the nodes in the pattern. If it were enough just to follow outgoing :transport relationships to related nodes (of particular labels) then we could separate the MATCH of nodes to update from the MATCH of the adjacent downstream nodes they need for each calculation.

I'm assuming that for some of these nodes during the MATCH, they have some adjacent downstream nodes that you don't want to sum() over, since further expansion from them won't be able to find a path matching the pattern?

The order of calculation has to be maintained because the formula if you see the query says that upstream node calculation is dependent on downstream node,

See the below formula of downstream_inflow_requirement:

match p=(a:RM)-[r1:transport]->(b:RM)-[r2:transport]->(c:RM)-[r3:transport]->(d:WIPProduction)-[r4:transport]->(e:WIPStorage)-[r5:transport]->(f:WIPStorage)-[r6:transport]->(g:WIPProduction)-[r7:transport]->(h:WIPStorage)-[r8:transport]->(i:WIPStorage)-[r9:transport]->(j:WIPStorage)-[r10:transport]->(k:Production)-[r11:transport]->(l:Storage)-[r12:transport]->(m:Storage)
where a.Date=date('2021-10-01')
with p,a,r1,b,r2,c,r3,d,r4,e,r5,f,r6,g,r7,h,r8,i,r9,j,r10,k,r11,l, 
sum((r12.quota)*(r12.bom_qty)*(m.Inflow_Requirement)) end as downstream_inflow_requirement
set l.Outflow_Requirement_Updated=downstream_inflow_requirement

Here, when we are on node l we want sum of the next node i.e. node m and next relationship r12.

Also I want to take sum of all the lower nodes which are connected to the upstream node if it is coming in this match clause.

I hope I am able to explain you properly, to make it a little bit more clearer I can take some other example where my path is shorter like

match p=(x:Production)-[q:transport]->(c:Storage)-[b:transport]->(d:Storage)
where x.date=date('2021-08-01') 
with x,q,c,b,d, case when d.end_point=1 then 0 else sum(e.quota*e.bom_qty*f.Inflow_Requirement) as downstream_inflow_req
set d.Outflow_Requirement_Updated=d.Outflow_Requirement+downstream_inflow_req

with x,q,c, case when c.end_point=1 then 0 else sum((b.quota*b.bom_qty*d.Inflow_Requirement)) as downstream_inflow_req
set c.Outflow_Requirement_Updated=c.Outflow_Requirement+downstream_inflow_req

with x,case when x.end_point=1 then 0 else sum((q.quota*c.Inflow_Requirement)) as downstream_inflow_req
set x.Outflow_Requirement_Updated=x.Outflow_Requirement+downstream_inflow_req;

This is the same formula just for shorter path, and my 2 issues remain same as mentioned in my first post.

Thanks,
Pragya