CODEWITHSUNDEEP
neo4jcypher
ryanpitts1
ryanpitts1

Reputation: 894

Neo4j Cypher find node where children do not have attribute value

Ok, I'm having trouble with a cypher query. Here is my query so far:

MATCH (oi:OrderItem)<-[:CONTAINS]-(o:Order)
WHERE oi.productSku IN ['600790041f','600790041s','600790061','600791021f','600791021s','600791051f','600791051s','600791061af','600791061as','600791061f','600791061s','600791101','600791111','600792011f','600792011s','600792031f','600792031s','600792041f','600792041s']
WITH DISTINCT o
RETURN COUNT(o) AS orders, AVG(o.orderBaseGrandTotal) AS avg;

What I am TRYING to do is return all orders that contain ONLY products from the list of SKUs above. The above query is giving me all orders that contain a product with one of those SKUs. However, I need to filter out orders that ALSO have products outside of that list of SKUs (i don't want those orders).

Essentially, I'm trying to say give me all orders that have only products in this list of SKUs and return to me the number of orders and the average order value for those orders. The part I am stuck on is filtering out orders that also have products outside that list of SKUs.

Upvotes: 2

Views: 270

Answers (1)

Dave Bennett
Dave Bennett

Reputation: 11216

You need to count the number of items in the sku list per order and then compare that to the total number of items per order. 

MATCH (oi:OrderItem)<-[:CONTAINS]-(o:Order)
WHERE oi.productSku IN ['600790041f','600790041s','600790061',...]

// get the total number of sku items per order
WITH o, count(*) as num_items

// only return the orders where the number of items in the order
// is equivalent to the number of sku items matched
WHERE size((o)-[:CONTAINS]->()) = num_items
RETURN COUNT(o) AS orders, AVG(o.orderBaseGrandTotal) AS avg;

Upvotes: 2

Related Questions