Update query for multiple cases

[tarendran_vivek]

Hi all,

I am trying to design a query that handles several cases of updating a particular node, however I am unable to achieve this.
Cases:

1. Update properties
2. Update relationship
3. Combination

Example:
Parameter

:param userdata: [{user:[{ID:'test1'}], car:[{ID:'test1'}, {ID:'test2'}]}]

UNWIND $userdata AS fields
WITH fields.user AS use, fields.car as vehicle
UNWIND use AS user
MATCH (u:User {ID:user.ID})
// Updating properties
SET u += user
WITH u, vehicle
UNWIND vehicle AS car

// Check if car is already assigned to another user
OPTIONAL MATCH (:Car {ID:car.ID})<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(us:User)
WHERE NOT EXISTS(cal.end_ts) AND NOT us.ID = user.ID
SET cal.end_ts=datetime()
WITH u, car

// Check if car is removed
OPTIONAL MATCH (cr:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(:User {ID: user.ID})
WHERE NOT EXISTS(cal.end_ts) AND NOT cr.ID = car.ID  
SET cal.end_ts=datetime()
WITH u, car

// Attach car to user
OPTIONAL MATCH (c:Car {ID:car.ID})
FOREACH (ignoreMe IN CASE WHEN c IS NULL THEN [] ELSE [1] END  | MERGE (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
ON CREATE SET cal.start_ts=datetime())
RETURN DISTINCT u AS data

I have two issues:

1. Say a User sells 1 of his cars and keeps the rest and I want to set my end time date to that car. However I cant achieve the above query.
2. What if a use sells his car and then later down decides to buy it back. A new node is not created.

I am not sure how to solve these 2 issues
Thanks in advance.

[accounts]

not sure if this is the kind of answer you're looking for, but imho this kinda logic better lives in a procedure rather than cypher. If you need help going down that path, i'll be more than happy 🙂 ( can reach out on slack as well )

[tarendran_vivek]

@accounts
I solved the first issue using the code below

UNWIND $userdata AS user
MATCH (u:User {ID:user.ID})
// Updating properties
SET u += user
WITH u, user

// Check if car is already assigned to another user
OPTIONAL MATCH (cr:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(us:User)
WHERE NOT EXISTS(cal.end_ts) AND NOT us.ID = user.ID AND cr.ID IN $cars
SET cal.end_ts=datetime()
WITH u

// Check if car is removed
OPTIONAL MATCH (cr:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
WHERE NOT EXISTS(cal.end_ts) AND NOT cr.ID IN $cars 
SET cal.end_ts=datetime()
WITH u

// Attach car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
FOREACH (ignoreMe IN CASE WHEN c IS NULL THEN [] ELSE [1] END  | MERGE (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
ON CREATE SET cal.start_ts=datetime())
RETURN DISTINCT u AS data

But I still have the issue when a user sells his car and then decides to buy it back, a new calendar node isnt created.

I know this query isn't valid but I think this gets my point across better than explaining it in just words

// For attaching car to user
OPTIONAL MATCH (c:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
WHERE c.ID IN $car

IF EXISTS(cal.end_ts)
-> Then create a new relationship
IF NOT EXISTS(cal.end_ts)
-> Then do nothing (as this means there is no change)
IF NOT EXISTS((c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u))
-> Then create a new relationship
IF the parameter $cars is empty
-> do nothing

[Cobra]

tarendran.vivekanand:

But I still have the issue when a user sells his car and then decides to buy it back, a new calendar node isnt created.

I don't see the line where you create a new calendar node?

[tarendran_vivek]

This is where I attach the car to the user

// Attach car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
FOREACH (ignoreMe IN CASE WHEN c IS NULL THEN [] ELSE [1] END  | MERGE (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
ON CREATE SET cal.start_ts=datetime())
RETURN DISTINCT u AS data

I use the foreach clause as there can be a possibility that the user has no cars so that parameter $cars will be empty.

[Cobra]

Could you try to replace the MERGE by CREATE?

[tarendran_vivek]

I use MERGE instead of CREATE for the case where I want to just update the user data and not change anything else

[Cobra]

According to the doc, you should use CREATE instead of MERGE:

When you use CREATE and a pattern, all parts of the pattern that are not already in scope at this time will be created.

[tarendran_vivek]

Yes but say I run the same query again. Then a duplicate relationship between car and user will be created

[Cobra]

Did you try?

[tarendran_vivek]

Yea I did. Unfortunately didn't work and created duplicate relationships

[Cobra]

Ok so try to MERGE the calendar node first and next CREATE relationships I think it must be done in several steps.

[tarendran_vivek]

That won't work for me. Let me explain it better
The query has the handle multiple cases of updating the user node
Changeable list

1. User properties
2. Attaching car nodes
3. Removing car nodes

So the query can handle any combination of these.
Note:

1. When attaching a car to user there is an intermediate node (calendar), this is to record the purchase date. Also since a car can only have 1 owner, I set the calendar node that is attach to the other owner to record the sale.
2. When removing a car from user the same intermediate node (calendar) is used to record the sale date.
3. If just updating the property and nothing else the same car parameter is still sent. Since this data is being sent, I have to make sure there is no change to their relationship

The query above can handle all of this except for 1 specific case which is when a user buys a car then sells the car but then decides to buy the exact same car back.
This is the result I get

To get his result you set parameter $cars = ["Test Car 1"]
then run the query then set the parameter to be empty, run the query again then set it back to ["Test Car 1"] and run it again and you will get the above result.

However this is what I need to get

[Cobra]

That's why you must CREATE a new calendar node first and then create the relationships to this new node

[tarendran_vivek]

My issue with that is the part when just updating the user properties and nothing else. But here I will still be passing in the $cars parameter with info. Thus I will be creating a duplicate node. I believe I found a solution using apoc.do.case but require some help with the conditions

// Attaching car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
WITH c, u
CALL apoc.do.case([
IF c IS NULL,
'RETURN u',
IF EXISTS ((c)<-[:ON]-(:Calendar)<-[:BOUGHT]-(u) WHERE 1 of the calendar nodes ONLY has start_ts),
'RETURN u'],
'CREATE RELATIONSHIP', // don't care about this line
{c:c, u:u}) YIELD value
RETURN value

[Cobra]

You can use EXISTS on properties check that start_ts exists and end_ts does not exist

[tarendran_vivek]

This is the query I am using

OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
WITH c, u
OPTIONAL MATCH (ca:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
WHERE ca.ID IN $cars
WITH c, u, cal
CALL apoc.do.case([
c IS NULL AND cal IS NULL,
'RETURN DISTINCT u AS rdata',
c is NOT NULL AND cal IS NULL,
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',
EXISTS(cal.start_ts) AND NOT EXISTS (cal.end_ts),
'RETURN DISTINCT u AS rdata'],
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',
{c:c,u:u,cal:cal}) YIELD value
RETURN value as data

so now the issue comes when I attach back the same car node then I run the query again with just the user property changed
Before:

After:

So a duplicate calendar node is created. I know it has to do with the exists() and not exists () conditions but I cant figure this out

[Cobra]

Weird, try to c IS NOT NULL AND cal IS NOT NULL

[tarendran_vivek]

@Cobra
Brilliant man. Just simply amazing
Thank you

[tarendran_vivek]

I am sorry, I didn't check everything but this still doesn't solve a particular issue.
The issue is where it doesn't create the relationship when you try to reconnect it back.
I.e. it doesnt go from this

to this

It seems like im in a loop
I believe the only way is to count the relationships between car and user but it will always return 1

// Attach car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
WITH c, u
OPTIONAL MATCH (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
WITH c, u, cal, count(cal) AS test
CALL apoc.do.case([
c IS NULL AND cal IS NULL,
'RETURN DISTINCT u AS rdata',
c is NOT NULL AND cal IS NULL,
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',
c is NOT NULL AND cal IS NOT NULL AND NOT EXISTS(cal.end_ts),
'RETURN DISTINCT u AS rdata',
c is NOT NULL AND cal IS NOT NULL AND EXISTS(cal.end_ts) AND test > 1,
'RETURN DISTINCT u AS rdata'],
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',
{c:c,u:u,cal:cal,test:test}) YIELD value
RETURN value as data

[Cobra]

c is NOT NULL AND cal IS NOT NULL AND EXISTS(cal.start_ts) NOT EXISTS(cal.end_ts)

Calendar must have a start but not an end

[tarendran_vivek]

Nope still doesn’t work,
I finally found out why. It is because there are 2 calendar nodes thus it is returned twice at the start of the call. One of them has a start and end while the other the start only. And at the start when u only have 1 calendar node with both start and end u won’t be able to create the new calendar node. That is why I tried to count the calendar node.

[Cobra]

I see, Does it work?

[tarendran_vivek]

I got upto this far but I am receving an error

// Attach car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
WITH c, u
OPTIONAL MATCH (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
with c, u, collect(cal) as b, count(cal) AS test
UNWIND b as beta
WITH c, u, beta, test
CALL apoc.do.case([
c IS NULL,
'RETURN DISTINCT u AS rdata',
c is NOT NULL AND beta.start_ts IS NULL,
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',
c is NOT NULL AND beta IS NOT NULL AND NOT EXISTS(beta.end_ts),
'RETURN DISTINCT u AS rdata',
c is NOT NULL AND beta IS NOT NULL AND EXISTS(beta.end_ts) AND test > 1,
'RETURN DISTINCT u AS rdata'],
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',
{c:c,u:u,beta:beta,test:test}) YIELD value
RETURN value as data

Error: NullCheckReferenceProperty(2,SlottedCachedPropertyWithPropertyToken(beta,PropertyKeyName(end_ts),2,false,2,3,NODE_TYPE,true)) (of class org.neo4j.cypher.internal.physicalplanning.ast.NullCheckReferenceProperty)
Using neo4j 4.1

[Cobra]

Try to replace EXISTS by IS NULL

[tarendran_vivek]

Well there is a new issue
When you try to add a new car that has no relationships to user the query stops at the UNWIND since it is empty.

// Attach car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
WITH c, u
OPTIONAL MATCH (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
with c, u, collect(cal) as b, count(cal) AS test
UNWIND b as beta
RETURN c, u, beta, test

So when I try to return this I get nothing. I am not sure how to solve this

[Cobra]

Why do you collect to unwind after?

[tarendran_vivek]

I collect so that I can get the total number of relationships needed, otherwise the number of relationships I get is 1

[Cobra]

You don't need to collect cal to count them

[tarendran_vivek]

If I dont collect it, I will get the number of cal nodes as 1. Im not sure if there is a better way?

[Cobra]

This with c, u, count(cal) AS test should work

[tarendran_vivek]

Yea that is what I was saying when I do that, I get test value as 1. Since I am getting individual rows of calendar nodes return. That is why I have to collect them.

[Cobra]

Hello @tarendran.vivekanand

Your query looks very complicated (to debug), you should try to make a procedure. Otherwise you must test each part one by one until you find the best way for each part and when you have all your parts, you could assemble them

Regards,
Cobra

[tarendran_vivek]

Hello @Cobra

Congrats for becoming a featured member

So I have broken it up into:

1. Updating user properties
2. Checking if the car assigned to user comes from another user (i.e. the current user has bought this car from another user)
3. Check if user has sold his car
4. Check if user has bought any cars

These are the 4 procedures and when assemble, the query can handle any combination of procedures
My assemble query:

UNWIND $userdata AS user
MATCH (u:User {ID:user.ID})
// Updating properties
SET u += user
WITH u, user

// Check if car is already assigned to another user
// If car is assigned to another user a timestamp is added
OPTIONAL MATCH (cr:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(us:User)
WHERE NOT EXISTS(cal.end_ts) AND NOT us.ID = user.ID AND cr.ID IN $cars
SET cal.end_ts=datetime()
WITH u

// Check if car is removed
// If user has sold his car, a timestamp is added
OPTIONAL MATCH (cr:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
WHERE NOT EXISTS(cal.end_ts) AND NOT cr.ID IN $cars 
SET cal.end_ts=datetime()
WITH u

// Assigning car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
WITH c, u
OPTIONAL MATCH (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
with c, u, collect(cal) as b, count(cal) AS test
UNWIND
   CASE
      WHEN b = []
         THEN [null]
      ELSE b
   END AS beta
WITH c, u, beta, test
CALL apoc.do.case([
// User owns no cars
c IS NULL,
'RETURN DISTINCT u AS rdata',

// User has bought car(s)
c is NOT NULL AND beta IS NULL,
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',

// Car(s) that user already owns
c is NOT NULL AND beta IS NOT NULL AND beta.end_ts IS NULL,
'RETURN DISTINCT u AS rdata',

// Car(s) that user has sold and now has decided to buy back
// CONDITION // Check all calendar nodes have both start and end timestamps (for particular car and user), if true then  
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata'],

// Do nothing otherwise
'RETURN DISTINCT u AS rdata',
{c:c,u:u,beta:beta,test:test}) YIELD value
RETURN value as data

My issue is with the 4th procedure (assigning car to user), I am not sure how the condition should be written (I have put comments indicating where). I am not sure how to check all the calendar nodes when the query is iterating for each row.

[Cobra]

Thank you

Try to work without the do case for the moment, just write a simple query to only handle the 4th procedure, when it will work, we could integrate it

Count the calendar node is a good idea, but you only need the one that does not have an end date right?

[tarendran_vivek · Accepted solution]

Hello @Cobra
Going back to basics worked. Thanks @Cobra
I finally solved it

UNWIND $userdata AS user
MATCH (u:User {ID:user.ID})
// Updating properties
SET u += user
WITH u, user

// Check if car is already assigned to another user
// If car is assigned to another user a timestamp is added
OPTIONAL MATCH (cr:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(us:User)
WHERE NOT EXISTS(cal.end_ts) AND NOT us.ID = user.ID AND cr.ID IN $cars
SET cal.end_ts=datetime()
WITH u

// Check if car is removed
// If user has sold his car, a timestamp is added
OPTIONAL MATCH (cr:Car)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
WHERE NOT EXISTS(cal.end_ts) AND NOT cr.ID IN $cars 
SET cal.end_ts=datetime()
WITH u

// Assigning car to user
OPTIONAL MATCH (c:Car)
WHERE c.ID IN $cars
WITH c, u
OPTIONAL MATCH (c)<-[:ON]-(cal:Calendar)<-[:BOUGHT]-(u)
with c, u, collect(EXISTS(cal.end_ts)) AS bool, count(deploy) AS numcalnode
WITH all(x in bool where x = true) as tester, numcalnode, c, u
CALL apoc.do.case([
// User owns no cars
c IS NULL,
'RETURN DISTINCT u AS rdata',

// User has bought car(s)
c is NOT NULL AND numcalnode = 0,
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata',

// Car(s) that user already owns
c is NOT NULL AND numcalnode >= 1 AND tester = FALSE,
'RETURN DISTINCT u AS rdata',

// Car(s) that user has sold and now has decided to buy back
c is NOT NULL AND numcalnode >= 1 AND tester = TRUE,
'CREATE (c)<-[:ON]-(z:Calendar)<-[:BOUGHT]-(u)
SET z.start_ts=datetime()
RETURN DISTINCT u AS rdata'],

// Do nothing otherwise
'RETURN DISTINCT u AS rdata',
{c:c,u:u,beta:beta,test:test}) YIELD value
RETURN value as data