query to compare two lists in CQL (Cypher)

0
votes

I have data pertaining to certain products and their purchase dates in a graph database in neo4j

Products 1. A, B, C, D, E, F, G Purchase dates 6th to 8th July

The purchase patterns are as follows

6th July - A,B,C,D 7th July - A,B,C,D,E 8th July - A,B,D,E,F,G

The purchase date is stored as an attribute in Order nodes. (i.e. o.purchase_date)

I wrote a Cypher query to count number of purchases per day

Match (o :Order)-[:Contains]->(p :Product) 
return o.purchase_date, count(p)

Output:
o.purchase_date       count(p)
6th July                   4
7th July                   5
8th July                   6

I want to modify this to query to tell me the total count of new products purchased each day, as compared to the previous day. i.e. 7th July 1 8th July 2

Any suggestions?

2
neo4jcomparecypher
Seems that returning this in a query can be fairly tough. It may be easier to construct and store this information in the db itself, but only if you only need it for orders with consecutive purchase dates, and without any additional filtering or criteria. Would that work for you, or does it need to be something solved entirely within a query? - InverseFalcon
The way this question is phrased makes me wonder if this is a homework or test problem, instead of a small example of a system you need to implement. In either case, I'd work with more varied sets of products for your test cases. With your current data there's only 1 or 2 new products, and you're never removing products, seems prone to false positives when testing solutions. Try some other combination, like 6th - A, B (2 new) 7th - B, C, D (2 new) 8th - A, B, C, D, E, F, G (4 new) 9th - A, Z (1 new) - InverseFalcon

2 Answers

1
votes

Use a filter function to compare lists:

WITH [ 'A', 'B', 'C', 'D']      as List1, 
     [ 'A', 'B', 'C', 'D', 'E'] as List2
RETURN FILTER ( product in List2 WHERE NOT product in List1 ) as newProduct

or list comprehension

WITH [ 'A', 'B', 'C', 'D']      as List1, 
     [ 'A', 'B', 'C', 'D', 'E'] as List2
RETURN [product in List2 WHERE NOT product in List1] as newProduct
1
votes

I think you'll want to approach this differently. Queries where operations must use data from the previous row are very hard to put together. Just getting to a point where you can reference data from two different rows at the same time is tough, and being able to perform that same operation between each set of two rows is another tough problem.

If you had a relationship between your orders instead, like a [:Next] between each in ascending order, then this headache of a problem becomes trivial.

The following query doesn't have ordering, I'll leave that to you, as your example suggests purchase dates might be in string format, which won't order properly as they are.

MATCH (o1:Order)-[:Next]->(o2:Order)-[:Contains]->(p2:Product)
WHERE NOT (o1)-[:Contains]->(p2)
WITH o1, o2, COLLECT(p2) AS NewProds
RETURN o1, o2, NewProds, SIZE(NewProds) AS NewProdCnt

This will return rows with pairs of orders, showing both new products and the count of new products between each order. You can easily remove o1 and/or NewProds from your return if you don't need them, which will only give you the order and the count of new products in that order.

The obvious shortfall here is that the very first of the order chain (where there is no previous order) is missing.

The following modification should fix that, if you need it:

MATCH (o1:Order)-[:Next*0..1]->(o2:Order)-[:Contains]->(p2:Product)
WHERE (o1 = o2 AND NOT (:Order)-[:Next]->(o2) AND (o2)-[:Next]->(:Order)) 
OR NOT (o1)-[:Contains]->(p2)
WITH o1, o2, COLLECT(p2) AS NewProds
RETURN o1, o2, NewProds, SIZE(NewProds) AS NewProdCnt

The :Next relationship between o1 and o2 is now optional, allowing this to match on orders with no preceding order, but we have to only count that ONLY when there is no preceding order (as otherwise every order could match with itself as both o1 and o2, instead of just the orders at the beginning of the chain), and it helps to add one additional condition that the order must have a next order. This means we won't get back anything for solitary orders, which could be desirable if the :Next relationships are only temporary, or created on the fly (to be removed when finished) between certain orders for running this kind of query; you wouldn't want to pick up any other orders you weren't interested in.

Lastly, there's the whole mess of creating the :Next relationships to begin with. This article has some very interesting stuff on working with linked lists in neo4j, but the part that's relevant to us right now looks something like this (copy and pasted from the article):

...
WITH elem ORDER BY elem.name ASC
WITH COLLECT(elem) AS elems
FOREACH (n IN RANGE(0, LENGTH(elems)-2) |
  FOREACH (prec IN [elems[n]] |
    FOREACH (next IN [elems[n+1]] |
      MERGE prec-[:Next]->next)))

You should replace the beginning and the first WITH clause with your own matching and ordering of interested orders. You'll collect them and iterate over that list creating the :Next relationships between them. You can of course use a different label, especially if you already have or plan to use :Next relationships between orders for other use cases (as that would mess up your query, and deletion of those relationships when you're done might accidentally get rid of unrelated relationships with the same label).

When I was setting up the next relationships, I ran into a curious thing where :Next relationships were created pointing from and to the same node. If that does happen to you, you'll want to clean it up after :Next relationship creation with:

MATCH (a:Order)-[r:Next]->(a)
DELETE r

And later on, if your :Next labels are temporary and you need to clean up after you've got the data you need, simply delete all :Next relationships between order nodes:

MATCH (:Order)-[r:Next]-(:Order)
DELETE r