How to merge two query that have same match clause?

[shrivastave2000]

HI team, I have a graph model as shown in picture.

I have requirement like first
1 . collect some properties from outer nodes and update sum of those property in middle node.
2 . by using this updated property in middle node I have to add new property in outer node.

//first query
MATCH p=(a)-[r:Is_Related_To]->(b:endPoint{name:"Product 3"}) 
with sum(a.outflow - a.inflow) as req_cap, b
set b.Required_Capacity = req_cap

second query :

MATCH p=(a)-[r:Is_Related_To]->(b:endPoint{name:"Product 3"}) 
 set r.outcome = b.Required_Capacity,
 a.result_cap = b.Required_Capacity + a.inflow
 return p

I want to merge these 2 queries. but as my graph has one to many relationship and when I am merging them, properties are not updating as expected.

Merged queries:

MATCH p=(a)-[r:Is_Related_To]->(b:endPoint{name:"Product 3"}) 
 with sum(a.outflow -a.inflow) as req_cap, b,r,a,p
 set b.Required_Capacity = req_cap
 set r.outcome = b.Required_Capacity,
 a.result_cap = b.Required_Capacity + a.inflow
 return p

I have tried to executing both set statements in seperate call subqueries but same problem occurs.

I am using 4.2.5 version in Neo4j Desktop.

[glilienfield]

Your merged solution does not give you the result you may expect. The issue is that your WITH statement has an aggregate function, along with values a, r, p. Since a, r, and p vary by row, the sum(a.outFlow-a.inFlow) is being calculate for each row of a, r, and p values. As a result, it is being calculated for each node a, row by row. The value of req_cap that is being sent to the query after the WITH clause is the last value calculated for the sum(a.outFlow-a.inFlow) from the last row; therefore, the value of req_cap is constant for all the values of a, r, and p in your set functions.

The following query seems to work. What makes it different is the sum value is being sent with the 'b' node, which is a single value and the sum is then done over the set of 'a' nodes associated with this one b node. This is similar to aggregate functions with the 'group by' clause in SQL.

Then a match is done again for the 'a' nodes given the 'b' node passed in the WITH clause, since the 'a' nodes from the previous match are out of scope after the WITH clause.

MATCH (a)-[r:Is_Related_To]->(b:endPoint{name:"Product 3"})
with sum(a.outflow -a.inflow) as req_cap, b
set b.Required_Capacity = req_cap
with b, req_cap
MATCH (a)-[r:Is_Related_To]->(b)
set r.outcome = req_cap, a.result_cap = req_cap + a.inflow
return a, r, req_cap, b