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 
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.
I see, Does it work?
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
Try to replace EXISTS by IS NULL
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
Why do you collect to unwind after?
I collect so that I can get the total number of relationships needed, otherwise the number of relationships I get is 1
You don't need to collect cal to count them
If I dont collect it, I will get the number of cal nodes as 1. Im not sure if there is a better way?
This with c, u, count(cal) AS test should work
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.
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
1 Like
Hello @cobra
Congrats for becoming a featured member 
So I have broken it up into:
- Updating user properties
- Checking if the car assigned to user comes from another user (i.e. the current user has bought this car from another user)
- Check if user has sold his car
- 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.
1 Like
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?
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
1 Like