Cypher diverging and converging path aggregate
<p>I have a question regarding aggregates over multiple paths that converges and then diverges again. Some of the aggregates should then take only a subset of the paths into account, while others more.</p>

<p>I'll explain this as best I could using an example of product manufacturing.
Let's say I've got a company producing 1 product, consisting of some material, supplied by a supplier.
To be more specific, this company produces 5 items of one product type, consisting of 10 grams of a material. Therefore during the manufacturing process they've used 50 grams of the material. But in production there's material waste, and they've actually used 70 grams, and wasted 20.</p>

<p>What I'd like to calculate is the corrected weight of material per product and supplier taking wastage into account. In this case, it's easy. 70g</p>

<p><a href="https://i.stack.imgur.com/cxz3Q.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cxz3Q.png" alt="simple production"></a></p>

<p>What happens when this becomes more complex:
<a href="https://i.stack.imgur.com/XMSag.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XMSag.png" alt="complex production"></a></p>

<p>Now the corrected weight for material1 per product1 and supplier1 is 58.82 grams.
This is the formula: </p>

<pre><code>material composition = sum(production amount * product composition)
corrected weight = (production amount * product composition * 
                     (purchased / (material composition)))
</code></pre>

<p>i.e. </p>

<pre><code>material composition = (5 * 10) + (20 * 40) = 850
corrected weight = (5 * 10 * (1000 / (850))) = 58.82
</code></pre>

<p>So, running a cypher query over this example should give me 6 results, as that is the number of permutations of products, materials and suppliers.</p>

<p>Question is, how to write such a query. I've tried reduce functions, repeated with's, etc, but it always seems to aggregate over the wrong set of nodes...</p>

<p>Just for completeness, here's the cypher to produce the graph:</p>

<p>Create:</p>

<pre><code>create (c:Company {name:'test', id:'c1'}),  
       (p1:Product {name:'product1', id:'p1'}),  
       (p2:Product {name:'product2', id:'p2'}), 
       (m1:Material {name:'material1', id:'m1'}), 
       (m2:Material {name:'material2', id:'m2'}), 
       (s1:Supplier {name:'supplier1', id:'s1'}), 
       (s2:Supplier {name:'supplier2', id:'s2'}), 
       (s3:Supplier {name:'supplier3', id:'s3'})
</code></pre>

<p>Rels:</p>

<pre><code>match (c:Company {id:'c1'}), 
    (p1:Product {id:'p1'}), 
    (m1:Material {id:'m1'})
    merge (c)&lt;-[pb_r1:PRODUCED_BY {amount:5}]-(p1)-[co_r11:CONSISTS_OF {amount:10}]-&gt;(m1)
    with c, p1, m1
    match (p2:Product {id:'p2'})
    merge (c)&lt;-[pb_r2:PRODUCED_BY {amount:20}]-(p2)-[co_r12:CONSISTS_OF {amount:40}]-&gt;(m1)
    with p1, p2, m1
    match (s1:Supplier {id:'s1'})
    merge (m1)-[pf_r1:PURCHASED_FROM {amount: 1000}]-&gt;(s1)
    with p1, p2
    match (m2:Material {id:'m2'})
    merge (p1)-[co_r21:CONSISTS_OF {amount:30}]-&gt;(m2)
    with p2, m2
    merge (p2)-[co_r22:CONSISTS_OF {amount:80}]-&gt;(m2)
    with m2
    match (s2:Supplier {id:'s2'})
    merge (m2)-[pf_r2:PURCHASED_FROM {amount: 1000}]-&gt;(s2)
    with m2
    match (s3:Supplier {id:'s3'})
    merge (m2)-[pf_r3:PURCHASED_FROM {amount: 1000}]-&gt;(s3)
</code></pre>