Cypher diverging and converging path aggregate

Question

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.

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.

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

What happens when this becomes more complex:

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

material composition = sum(production amount * product composition)
corrected weight = (production amount * product composition * 
                     (purchased / (material composition)))

i.e.

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

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.

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...

Just for completeness, here's the cypher to produce the graph:

Create:

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'})

Rels:

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

stdob-- · Accepted Answer · 2016-05-15T15:23:11.913

1

// Selection of the supply chain and production by Company 
//
MATCH (C:Company {id:'c1'})
        <-[pb:PRODUCED_BY]-
      (P:Product)
        -[co:CONSISTS_OF]->
      (M:Material)
        -[pf:PURCHASED_FROM]->
      (S:Supplier)

// Grouping by materials, calculation material composition,
// and the preservation of the chain to the supplier
// 
WITH M,
     S, // group by supplier 
     SUM(pb.amount*co.amount) as mComp, 
     collect({
       product:P,
       prod: pb.amount, 
       comp: co.amount, 
       purchased: pf.amount
     }) as tmps

// Calculating the correct weight by material and supplier
// 
UNWIND tmps as tmp
RETURN M as material, 
       tmp['product'] as product, 
       S as supplier,
       1.0 * tmp['prod'] * tmp['comp'] * tmp['purchased'] / mComp as cWeight

edited May 15 '16 at 15:23

answered May 15 '16 at 14:48

stdob--

28,222
5
58
73

+1 for the quick result! Almost correct, there's still just one issue, which I struggled with from the beginning. The mComp sum is being calculated over suppliers paths too. So, we're getting 3500, instead of 1750 for material2. Somehow I need to be able to get material paths before supplier paths are calculated. But matching twice would lead to additional paths anyway... – superkruger May 15 '16 at 15:15
Run this to see what I mean:MATCH (C:Company {id:'c1'}) <-[pb:PRODUCED_BY]- (P:Product) -[co:CONSISTS_OF]-> (M:Material) -[pf:PURCHASED_FROM]-> (S:Supplier) WITH M, SUM(pb.amount*co.amount) as mComp, collect({ product:P.name, prod: pb.amount, comp: co.amount, purchased: pf.amount, supplier: S.name }) as tmps UNWIND tmps as tmp RETURN tmp['product'] as product, M.name as material, tmp['supplier'] as supplier, mComp,1.0 * tmp['prod'] * tmp['comp'] * tmp['purchased'] / mComp as cWeight – superkruger May 15 '16 at 15:16
Without the SUM, it gives individual material compositions based on products. But with the SUM, it also sums over suppliers: MATCH (C:Company {id:'c1'}) <-[pb:PRODUCED_BY]- (P:Product) -[co:CONSISTS_OF]-> (M:Material) -[pf:PURCHASED_FROM]-> (S:Supplier) WITH M, (pb.amount*co.amount) as mComp, collect({ product:P.name, prod: pb.amount, comp: co.amount, purchased: pf.amount, supplier: S.name }) as tmps UNWIND tmps as tmp RETURN tmp['product'] as product, M.name as material, tmp['supplier'] as supplier, mComp,1.0 * tmp['prod'] * tmp['comp'] * tmp['purchased'] / mComp as cWeight – superkruger May 15 '16 at 15:20
@superkruger - I have updated the query (add grouping by the supplier) - now the amount has to be correct. – stdob-- May 15 '16 at 15:24

Cypher diverging and converging path aggregate

1 Answers1