CODEWITHSUNDEEP
node.jsneo4j
Ching Yaw Hao
Ching Yaw Hao

Reputation: 84

Neo4j not returning nested records consistently

Example

I have these nested nodes that I wanted to extract out. A one to many to many relationships of nodes. I used the following cypher to do it.

WITH (o:order)
WHERE ID(o) = <some id>
OPTIONAL MATCH (o) -[:ORDER_INCLUDES]-> (p:pizza)
WITH o, p
OPTIONAL MATCH (p) -[:WITH_TOPPING]-> (t:topping)
WITH o, {
  pizza: p,
  toppings: COLLECT(DISTINCT t)
} AS pi
RETURN o, COLLECT(DISTINCT pi) AS pizzas

When theres none of the pizza with toppings, I get the result I wanted which is 1 record or order with the pizzas. Note that the toppings are always optional.

1. Order     [
               Tomato Pizza,
               Tomato Pizza
             ]

However when theres toppings involved, it returns the pizza in separate records

1. Order     [                  [
               Tomato Pizza       Pineapple Topping,
             ]                    Chicken Topping
                                ]
2. Order     [
               Tomato Pizza
             ]

Which isn't what I want since the 2 pizzas belong to the same orders. I must be doing something wrong, please help.

Edited:--

Sorry, I missed out a huge detail that causes this problem. I collected the time for when the pizza is created at the beginning.

WITH (o:order)
WHERE ID(o) = <some id>
OPTIONAL MATCH (o) -[:ORDER_INCLUDES]-> (p:pizza)
OPTIONAL MATCH (e:event) WHERE (e) -[:FOR]-> (o) OR (e) -[:FOR]-> (pizza)
WITH o, MAX(e.time) AS time, p
OPTIONAL MATCH (p) -[:WITH_TOPPING]-> (t:topping)
OPTIONAL MATCH (p) <-[:FOR]- (e:event)
WITH o, {
  pizza: p,
  time: MAX(e.time),
  toppings: COLLECT(DISTINCT t)
} AS pi
RETURN o, time, COLLECT(DISTINCT pi) AS pizzas

That time was the culprit, this problem is resolved when I remove the time.

Upvotes: 1

Views: 35

Answers (2)

Ching Yaw Hao
Ching Yaw Hao

Reputation: 84

To prevent the record from splitting, the following had to be removed

OPTIONAL MATCH (e:event) WHERE (e) -[:FOR]-> (o) OR (e) -[:FOR]-> (pizza)

Now this is the solution I'm working with

WITH (o:order)
WHERE ID(o) = <some id>
OPTIONAL MATCH (o) -[:ORDER_INCLUDES]-> (p:pizza)
WITH o, p
OPTIONAL MATCH (p) -[:WITH_TOPPING]-> (t:topping)
OPTIONAL MATCH (p) <-[:FOR]- (e:event)
WITH o, {
  pizza: p,
  time: MAX(e.time),
  toppings: COLLECT(DISTINCT t)
} AS pi
RETURN o, MAX(pi.time) AS time, COLLECT(DISTINCT pi) AS pizzas

Upvotes: 0

Graphileon
Graphileon

Reputation: 5385

This should work (first collect the toppings for each pizza, then collect the pizzas and their toppings)

MATCH (o:order)
WHERE id(o) = $id
OPTIONAL MATCH (o)-[:ORDER_INCLUDES]-> (p:pizza)
WITH o, p
OPTIONAL MATCH (p) -[:WITH_TOPPING]-> (t:topping)
WITH o,p, COLLECT(t) AS toppings
RETURN o,COLLECT ({pizza:p,toppings:toppings}) AS pizzas

Buon appetito

Upvotes: 1

Related Questions