I am trying to bulk add a user with his cars. Procedure:
Create user node
Check if the car added is connected to other users and if yes then place a timestamp
Create relationship between user and cars, on create set a timestamp
UNWIND $data AS fields
WITH fields.user AS user, fields.car AS car
UNWIND user AS row
// Create user node(s)
CREATE (u:User {ID:apoc.create.uuid()})
SET u += row
WITH u, car
// Update assign node if car is already assigned to another user
UNWIND car AS cars
OPTIONAL MATCH (c:Car)
WHERE c.ID = cars.ID
OPTIONAL MATCH (:User)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)
WHERE NOT EXISTS(an.end_ts)
SET an.end_ts=datetime()
WITH u, c
// Assign car(s) to user
CALL apoc.do.when(
c IS NULL,
'RETURN u AS rdata',
'CREATE (u)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)
SET an.start_ts=datetime()
RETURN u AS rdata',
{u:u,c:c}) YIELD value
RETURN value AS data
My input: data: [{user:[{name:'testuser1'}], car:[{ID:'test1'}, {ID:'test2'}]}, {user:[{name:'testuser2'}], car:[{ID:'test3'}, {ID:'test2'}]}]
For this case where the input for the car is the same for both users, the query won't set a timestamp to testuser1, I am not sure why or how to solve this?
Hello @cobra
Yes I tested it separately and both update assign node and assign car to user works as intended. When they are together as above it works when the input is data: [{user:[{name:'testuser1'}], car:[{ID:'test1'}, {ID:'test2'}]}, {user:[{name:'testuser2'}], car:[{ID:'test3'}, {ID:'test4'}]}]
However if the input has the same car ID entered for both user testuser1 & testuser2 only then the query doesn't work as intended. (i.e. no end_ts is added to testuser1)
UNWIND $data AS fields
WITH fields.user AS user, fields.car AS car
UNWIND user AS row
// Create user node(s)
CREATE (u:User {ID:apoc.create.uuid()})
SET u += row
WITH u, car
UNWIND car AS cars
OPTIONAL MATCH (c:Car)
WHERE c.ID = cars.ID
WITH u, c
// Assign car(s) to user
CALL apoc.do.when(
c IS NULL,
'RETURN u AS rdata',
'CREATE (u)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)
SET an.start_ts=datetime()
RETURN u AS rdata',
{u:u,c:c}) YIELD value
RETURN value AS data
Here I just added cars to user and checked if the user has the relationship to car and the start_ts
2nd test code:
UNWIND $data AS fields
WITH fields.user AS user, fields.car AS car
UNWIND user AS row
// Create user node(s)
CREATE (u:User {ID:apoc.create.uuid()})
SET u += row
WITH u, car
// Update assign node if car is already assigned to another user
UNWIND car AS cars
OPTIONAL MATCH (c:Car)
WHERE c.ID = cars.ID
OPTIONAL MATCH (user:User)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)
WHERE NOT EXISTS(an.end_ts)
SET an.end_ts=datetime()
RETURN u, c, user
Here if the car comes from another user (that has already been added), I check if there is an end_ts for the relationship between car (c) and user (user)
That match is to check if the car assigned is attached to other user. If yes then set end timestamp, if not don't do anything. So switching it with the user u just created will break its purpose.
CALL apoc.do.when(
c IS NULL,
'RETURN u AS rdata',
'CREATE p = (u)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)
WITH u, an
SET an.start_ts=datetime()
RETURN u AS rdata',
{u:u,c:c}) YIELD value
Unfortunately that didn't work
Somehow I got it to work using the query below.
UNWIND $data AS fields
WITH fields.user AS user, fields.car AS car
UNWIND user AS row
// Create user node(s)
CREATE (u:User {ID:apoc.create.uuid()})
SET u += row
WITH u, car
// Assign car(s) to user
UNWIND car AS cars
OPTIONAL MATCH (c:Car)
WHERE c.ID = cars.ID
CALL apoc.do.when(
c IS NULL,
'RETURN u AS rdata',
'CREATE (u)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)
SET an.start_ts=datetime()
RETURN u AS rdata',
{u:u,c:c}) YIELD value
WITH u, c
// Update assign node if car is already assigned to another user
OPTIONAL MATCH (user:User)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)
WHERE NOT EXISTS(an.end_ts) AND NOT user.ID = u.ID
SET an.end_ts=datetime()
RETURN u AS data
However I don't understand why switching assigning car to user and update assign node makes it work?
@cobra I will always need you
I put it there now cause I created the relationship first then only setting the end timestamp but before I didn't create the relationship so I won't need it. I am crying in confusion
)
