neo4jcypher

How to list historical nodes for each current node in Cypher?

First of all, I have created this sample graph DB through Cypher like this:

// Clients
CREATE (Alice:Client {name:'Alice', ip: '1.1.1.1', shipping_address: 'a place', billing_address: 'a place'})
CREATE (Bob:Client {name:'Bob', ip: '1.1.1.2', shipping_address: 'b place', billing_address: 'b place'})
CREATE (Cindy:Client {name:'Cindy', ip: '1.1.1.3', shipping_address: 'c place', billing_address: 'c place'})
CREATE (Diana:Client {name:'Diana', ip: '1.1.1.4', shipping_address: 'd place', billing_address: 'd place'})
CREATE (Emily:Client {name:'Emily', ip: '1.1.1.5', shipping_address: 'e place', billing_address: 'e place'})
CREATE (Fiona:Client {name:'Fiona', ip: '1.1.1.6', shipping_address: 'f place', billing_address: 'f place'})

// Products
CREATE (prod1:Product {name: 'strawberry ice-cream', category: 'ice-cream', price: 6.9, unit: 'box'})
CREATE (prod2:Product {name: 'mint ice-cream', category: 'ice-cream', price: 6.9, unit: 'box'})
CREATE (prod3:Product {name: 'mango ice-cream', category: 'ice-cream', price: 6.9, unit: 'box'})
CREATE (prod4:Product {name: 'cheesecake ice-cream', category: 'ice-cream', price: 7.9, unit: 'box'})
CREATE (prod5:Product {name: 'orange', category: 'furit', unit: 'lb', price: 2.6, unit: 'box'})
CREATE (prod6:Product {name: 'dragon fruit', category: 'furit', unit: 'lb', price: 4.8, unit: 'box'})
CREATE (prod7:Product {name: 'kiwi', category: 'furit', unit: 'lb', price: 5.3, unit: 'box'})
CREATE (prod8:Product {name: 'cherry', category: 'furit', unit: 'lb', price: 4.8, unit: 'box'})
CREATE (prod9:Product {name: 'strawberry', category: 'furit', unit: 'lb', price: 3.9, unit: 'box'})

// Orders
CREATE (d1:Order {id:'d1', name:'d1', deliverdate:'20190410', status:'delivered'})
CREATE (d2:Order {id:'d2', name:'d2', deliverdate:'20130708', status:'delivered'})
CREATE (d3:Order {id:'d3', name:'d3', deliverdate:'20021201', status:'delivered'})
CREATE (d4:Order {id:'d4', name:'d4', deliverdate:'20040612', status:'delivered'})
CREATE (d5:Order {id:'d5', name:'d5', deliverdate:'20110801', status:'delivered'})
CREATE (d6:Order {id:'d6', name:'d6',deliverdate:'20171212', status:'delivered'})

// Link Clients, Orders and ProductsCREATE
CREATE
    (Alice)-[:PLACED]->(d1)-[:CONTAINS {quantity:1}]->(prod1),
    (d1)-[:CONTAINS {quantity:2}]->(prod2),
    (Bob)-[:PLACED]->(d2)-[:CONTAINS {quantity:2}]->(prod1),
    (d2)-[:CONTAINS {quantity:6}]->(prod7),
    (Cindy)-[:PLACED]->(d3)-[:CONTAINS {quantity:1}]->(prod9),
    (Alice)-[:PLACED]->(d4)-[:CONTAINS {quantity:100}]->(prod4),
    (Alice)-[:PLACED]->(d5)-[:CONTAINS {quantity:10}]->(prod8),
    (Alice)-[:PLACED]->(d6)-[:CONTAINS {quantity:1}]->(prod7)

Here's the list of orders from Alice: enter image description here

The output I hope to get is, for each row, get the list of orders that happened before current order. For example:

c.name  o.id  historical_orders
"Alice"  "d4"       
"Alice"  "d5"      ["d4"]
"Alice"  "d6"      ["d4", "d5"]
"Alice"  "d1"      ["d4", "d5", "d1"]

Is there anyway in Cypher to do this?

Solution

  • This query:

    MATCH (c:Client {name:'Alice'})-[:PLACED]->(o)
WITH c, o ORDER BY o.deliverdate
WITH c.name AS name, COLLECT(o) AS os
UNWIND [i IN RANGE(0, SIZE(os)-1) |
  {name: name, id: os[i].id, history: [x IN os[0..i] | x.id]}] AS result
RETURN result

    produces this result:

    ╒═════════════════════════════════════════════════════╕
│"result"                                             │
╞═════════════════════════════════════════════════════╡
│{"name":"Alice","id":"d4","history":[]}              │
├─────────────────────────────────────────────────────┤
│{"name":"Alice","id":"d5","history":["d4"]}          │
├─────────────────────────────────────────────────────┤
│{"name":"Alice","id":"d6","history":["d4","d5"]}     │
├─────────────────────────────────────────────────────┤
│{"name":"Alice","id":"d1","history":["d4","d5","d6"]}│
└─────────────────────────────────────────────────────┘