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:
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.
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
// 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.
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
User properties
Attaching car nodes
Removing car nodes
So the query can handle any combination of these.
Note:
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.
When removing a car from user the same intermediate node (calendar) is used to record the sale date.
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.
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
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:
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
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
I think it must be done in several steps.
check that 
