Bulk add not performing as expected

[tarendran_vivek]

Hi,

I am trying to bulk add a user with his cars. Procedure:

1. Create user node
2. Check if the car added is connected to other users and if yes then place a timestamp
3. 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

PROFILE:

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?

Thanks in advance.

[Cobra]

Hello @tarendran.vivekanand

Did you try to test only the part of the query that was doing the wrong thing?

Regards,
Cobra

[tarendran_vivek]

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)

[Cobra]

Can you show the show the part of the query you tested? (To compare with the first one you gave)

[tarendran_vivek]

Sure,

1st 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

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)

[Cobra]

I think the issue is coming from the OPTIONAL keyword, it can be source of lot of bugs

Why are you using it your case?

[tarendran_vivek]

Unfortunately Yes
I am using it as a user can be added without any cars being assigned to him

[Cobra]

Is something returned by the query when there is the bug?

[tarendran_vivek]

Yes,

So when my input is:
data: [{user:[{name:'testuser1'}], car:[{ID:'test1'}]}, {user:[{name:'testuser2'}], car:[{ID:'test1'}]}]

I get this relationship:

But I should have an end_ts on assign node for user testuser1

[Cobra]

It should be OPTIONAL MATCH (u:User)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c) instead of OPTIONAL MATCH (user:User)<-[:ON]-(an:Assign)<-[:SCHEDULED]-(c)?

[tarendran_vivek]

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.

[Cobra]

Ok

Can you try:

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

[tarendran_vivek · Accepted solution]

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]

Good job (you don't need me )

I think it's because of the condition NOT user.ID = u.ID

[tarendran_vivek]

@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

[Cobra]

It will be hard for Neo4j to set a property if there is no relationship

Everything good now?

[tarendran_vivek]

but there is a relationship unless I don't understand the UNWIND function that well. I assume that it will go through the loop each time for each row.

[Cobra]

Yes UNWIND will go through each row but the OPTIONAL can break it, if there is something that does not exist in the OPTIONAL, it can do weird stuff

[tarendran_vivek]

Thank you @Cobra