How to partition a complex set

[paolodipietro58]

I have the following problem:

I have a cart, which contains several products from different vendor. The structure is as follow:

     MATCH (cart)-[contains:CONTAINS]->(product:Product)-[sold:IS_SOLD_BY]->(merchant:Merchant)

contains.quantity is the quantity of each product sold in the cart.

I would like to transform the above structure creating many orders, one for each different merchant,

MATCH (cart)-[contains:CONTAINS]->(product:Product)-[sold:IS_SOLD_BY]->(merchant:Merchant)
 
     WITH cart, contains, product, merchant
       
     CREATE (order:Order)-[b:BELONGS_TO]->(merchant)
          set order.productValue = product.price + contains.quantity
          set order.transport = 9 + contains.quantity
          set order.status = "created"

     CREATE (order)-[c:CONTAINS]->(product)
               set c=contains

    RETURN order

but this returns n orders one for each product

I tried different solution, but was unable to create what I want.

Any suggestion will be appreciated

[glilienfield]

I think something like this should work.

MATCH (cart)-[contains:CONTAINS]->(product:Product)-[sold:IS_SOLD_BY]->(merchant:Merchant)
WITH merchant, collect({c: contains, p: product}) as merchantProducts

CREATE (order:Order)-[b:BELONGS_TO]->(merchant)
          //set order details

WITH order, merchantProducts
UNWIND merchantProducts as merchantProduct
WITH order, merchantProduct.c as contains, merchantProduct.p as product
MERGE (order)-[c:CONTAINS]->(product)
             set c=contains

 RETURN distinct(order)

I originally tried the following code with a foreach loop, as the code is more compact and direct. Cypher didn't like the syntax of passing the 'i.p' value. The UNWIND and WITH clause were to get around this.

MATCH (cart)-[contains:CONTAINS]->(product:Product)-[sold:IS_SOLD_BY]->(merchant:Merchant)
 
     WITH merchant, collect({c: contains, p: product}) as merchantProducts
       
     CREATE (order:Order)-[b:BELONGS_TO]->(merchant)
          //set order details

     forEach(i in merchantProducts | 
        merge (order)-[c:CONTAINS]->(i.p)
             set c=i.c
     )
    RETURN order

If you need the 'product' and 'contains' details for the order creation, then you can use 'list comprehension' and 'list reduce' to extract it. You can also add additional attributes to the map with calculations needed for order creation, such as the product of the product price and quantity. You can then use list reduce to get the total value of the order. I can help with that if this approach works and meets your needs.

[paolodipietro58]

I tried to introduce some calculation for the totals, but it failed.
I tried with

CREATE (order:Order)-[b:BELONGS_TO]->(merchant)
        set order.productPrice=reduce(acc=0,item in merchantProducts | acc + item.product.price)
        set order.transportPrice = 9 + count(merchantProducts)

but it failed with two error:

Invalid use of aggregating function count(...) in this context (line 6, column 42 (offset: 353))
"          set order.transportPrice = 9 + count(merchantProducts)"
                                          ^

and in any case, removing the offending line, there is no ProductPrice computed.

How can I use list comprehension to compute the ProductPrice and the TransportPrice for each order?

[glilienfield · Accepted solution]

I overlooked this when writing the original post, but it is easiest doing this using the aggregate functions:

MATCH (cart)-[contains:CONTAINS]->(product:Product)-[sold:IS_SOLD_BY]->(merchant:Merchant)
WITH merchant, collect({c: contains, p: product}) as merchantProducts, count(product) as noOfProducts, sum(product.price*contains.quantity) as orderTotalPrice

CREATE (order:Order)-[b:BELONGS_TO]->(merchant)
          //set order details

WITH order, merchantProducts
UNWIND merchantProducts as merchantProduct
WITH order, merchantProduct.c as contains, merchantProduct.p as product
MERGE (order)-[c:CONTAINS]->(product)
             set c=contains

RETURN distinct(order)

Note, I put the quantity in the total formula in case you wanted it and to show it could be done there. Remove it if it is not what you want for the sum.

Sorry for my oversight, but let’s still discuss the code to understand what is occurring. I see two things that would cause issues. First, the product info was added to the map with a key of ‘p’, so you need to reference it that way in the reduce clause. Second, merchantProducts is a list, so you need to use the ‘size’ method. Count is an aggregate function used to count records of a result set.

CREATE (order:Order)-[b:BELONGS_TO]->(merchant)
        set order.productPrice=reduce(acc=0,item in merchantProducts | acc + item.p.price)
        set order.transportPrice = 9 + size(merchantProducts)

[paolodipietro58]

Just to add a little bit of complexity:

What if I want to add a consecutive orderNumber to each new order, starting from the latest orderNumber + 1?

I found nothing looking around!