Update query for multiple cases

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.

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 )

@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

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

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.

Could you try to replace the MERGE by CREATE?

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

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.

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

Did you try? :smile:

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

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

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

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

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

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

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

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

1 Like

@Cobra
Brilliant man. Just simply amazing :slight_smile:
Thank you

1 Like

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