Need help with query

Neo4j Graph Platform Cypher
1

Hello community. I have this query:

MATCH(o:Order {pickupDate:$pickupDate,status:$status})
MATCH (o)-[r:CONTAINS_ITEM]->(oi:OrderItem)
OPTIONAL MATCH (oi)<-[pck:PICKED]-(po:PreparedOrder)<-[:PREPARED_AS]-(o)
OPTIONAL MATCH (oi)-[:ALTERNATIVE]->(oia:OrderItem:Alternative)
OPTIONAL MATCH (oi)<-[a:ALTERNATIVES_REASON]-(po)
WITH o, po, oi,pck,r, a, collect(distinct(oia)) as alternatives
WITH o, po,oi,pck,r, collect({ alternativesCollected: po.alternativesCollected,reason: a, alternatives:alternatives}) as items
WITH o,collect({weighing:oi.weighing,EAN:oi.id,quantity:r.withQuantity,name:oi.name,price:oi.price,weight:oi.weight,group:oi.group,alt:items,collectedQuantity:pck.quantity}) as mam
RETURN {id:o.id, comment:o.comment,total:o.id, pickupDate:o.pickupDate, pickupTime:o.pickupTime, pickupLocation:o.pickupLocation, paymentOption: o.paymentOption, customerPhone: o.customerPhone,status:o.status,items:mam} as preparedOrder

Issue with query is that each time when order completed it return not for example 10 items then 20 items, somehow it doubles items ( things with oi ). Any idea what is wrong?

2

Hi,

Try replacing this: (oia:OrderItem:Alternative) (in line 4) with
(oia:Alternative) as you want to select alternative items added to the order.

-Kamal